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.
Anonymous,
Based on your first screenshot and your sample data, the Metric Cumulative value for January of 2014, 2015, 2016,2017 and 2018 years don't equal to 0, do you want to set the Metric Cumulative value to be 0? If so, could you please share more data of "Raw data" table for us to test?
Regards,
Lydia
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.