Forum Discussion
Another Running Total Question
OK turns out this was way easier than I was making it. Instead of messing with the formula and doing everything 'by force', there is a Quick Measure for Year to Date. I thought at the time I was playing with it that the YTD would only work for data in the current year, but it will actually work for multiple years. I was able to add a YTD version of the metric and it worked perfectly and gave me the exact graph I was looking for there.
Right, January wouldn't be equal to 0, but Jan 1st of the year at midnight would be 0.
Basically my data is very granular, and I have rows of data for almost every hour of every day. I would need it to reset to zero exactly at midnight, but even by 1am on new year's day I'll probably have data. Below is a moderately anonymized version of the table. Dates and stuff are the same. I have calculated columns that don't show here for Year(start date), month(start date), day(Start date). Also have Year&Month (ie 201804), and year month territory (ie 201804A). The customer table also has year month territory column (ie 201804A) which is what the relationship is based on.
| ID | Start Date | End Date | Status | Metric Component 2 | Length | Metric Component | Territory | C# | UODID | Cause | View | Source |
| 15598583 | 4/1/13 0:00 | 4/1/13 14:46 | CMP | 0 | 886.4166667 | 0 | A | 839.2634767 | 350.295675 | Outside Factor | PM | 1.7 |
| 15598584 | 4/1/13 0:00 | 4/1/13 18:23 | CMP | 0 | 1103.383333 | 0 | B | 3177.056381 | 39.37354854 | Planned Request Customer | PM | 1.7 |
| 15598585 | 4/1/13 0:21 | 4/1/13 1:10 | CMP | 2 | 49 | 98 | C | 36660.45295 | 3067.457455 | Failure Wear | PM | 1.7 |
| 15598586 | 4/1/13 1:51 | 4/1/13 5:06 | CMP | 0 | 195.3166667 | 0 | D | 2694.555091 | 2148.102147 | Outside Factor Fire | PM | 1.7 |
| 15598587 | 4/1/13 2:24 | 4/1/13 4:45 | CMP | 8 | 140.4666667 | 1123.73333 | D | 6912.50926 | 5185.59996 | Outside Factor Fire | PM | 1.7 |
| 15598588 | 4/1/13 4:21 | 4/1/13 5:50 | CMP | 2 | 88.26666667 | 176.533333 | E | 5885.557965 | 4763.835065 | Animal | PM | 1.7 |
| 15598589 | 4/1/13 6:19 | 4/1/13 6:53 | CMP | 1 | 33.86666667 | 33.8666667 | C | 39680.6477 | 36486.94172 | Unselected | PM | 1.7 |
| 15598590 | 4/1/13 7:18 | 4/1/13 9:15 | CMP | 1 | 116.6833333 | 116.683333 | F | 4205.015999 | 1036.788266 | Animal | PM | 1.7 |
| 15598591 | 4/1/13 7:20 | 4/1/13 7:36 | CNL | 1 | 15.85 | 15.85 | Warrensburg | 6204.30426 | 1094.586421 | Unknown | PM | 1.7 |
| 15598592 | 4/1/13 7:21 | 4/1/13 9:01 | CMP | 0 | 99.7 | 0 | C | 2313.85771 | 1892.462309 | Unselected | PM | 1.7 |
| 15598593 | 4/1/13 7:22 | 4/1/13 8:36 | CMP | 0 | 74 | 0 | C | 2284.255074 | 1519.403311 | Unselected | PM | 1.7 |
| 15598594 | 4/1/13 7:37 | 4/12/13 18:00 | CNL | 0 | 16463.08333 | 0 | A | 313.0291175 | 156.1489578 | Unknown | PM | 1.7 |
| 15598595 | 4/1/13 7:54 | 4/1/13 13:53 | CMP | 0 | 358.2833333 | 0 | G | 18594.87236 | 6179.357804 | PM | 1.7 | |
| 15598596 | 4/1/13 8:08 | 4/1/13 11:01 | CMP | 1 | 172.6166667 | 172.616667 | H | 19719.91549 | 18605.85861 | PM | 1.7 | |
| 15598597 | 4/1/13 8:10 | 4/1/13 12:53 | CMP | 0 | 282.65 | 0 | A | 44.9209252 | 29.55197266 | Unselected | PM | 1.7 |
| 15598598 | 4/1/13 8:13 | 4/2/13 12:45 | CMP | 0 | 1711.6 | 0 | A | 2765.036062 | 1001.251323 | Failure Wear | PM | 1.7 |
| 15598599 | 4/1/13 8:14 | 4/1/13 17:40 | CMP | 2 | 565.3 | 1130.6 | I | 2338.265835 | 834.2864042 | Planned Request | PM | 1.7 |
| 15598600 | 4/1/13 8:15 | 4/1/13 11:54 | CMP | 0 | 219 | 0 | C | 25071.04088 | 16298.84957 | Unselected | PM | 1.7 |
| 15598601 | 4/1/13 8:17 | 4/1/13 17:03 | CMP | 1 | 525.7666667 | 525.766667 | J | 2118.356997 | 414.0393082 | Planned Request | PM | 1.7 |
| 15598602 | 4/1/13 8:21 | 4/1/13 9:16 | CMP | 0 | 54.1 | 0 | K | 25165.46525 | 210.0593581 | Vegetation Trees | PM | 1.7 |
| 15598603 | 4/1/13 8:21 | 4/3/13 12:59 | CMP | 0 | 3157.6 | 0 | L | 7841.892158 | 1030.431465 | Unselected | PM | 1.7 |
| 15598604 | 4/1/13 8:30 | 4/1/13 11:13 | CMP | 1 | 162.2 | 162.2 | G | 22797.22658 | 10372.7451 | Failure Wear | PM | 1.7 |
OK turns out this was way easier than I was making it. Instead of messing with the formula and doing everything 'by force', there is a Quick Measure for Year to Date. I thought at the time I was playing with it that the YTD would only work for data in the current year, but it will actually work for multiple years. I was able to add a YTD version of the metric and it worked perfectly and gave me the exact graph I was looking for there.