Hello Everyone
I'm trying to use CumulativePeriod function in VA to calculate a cumulative result over a period, i found result is quite strange as below:
Frequency | cummlated number of claims | Repair Date |
43 | 43 | 2014-02 |
13 | 56 | 2014-03 |
70 | 126 | 2014-04 |
114 | 240 | 2014-05 |
303 | 543 | 2014-06 |
77 | 620 | 2014-07 |
182 | 802 | 2014-08 |
496 | 1,298 | 2014-09 |
577 | 1,875 | 2014-10 |
406 | 2,281 | 2014-11 |
562 | 2,843 | 2014-12 |
1,266 | 1,266 | 2015-01 |
1,008 | 2,274 | 2015-02 |
1,263 | 3,537 | 2015-03 |
894 | 4,431 | 2015-04 |
2,126 | 6,557 | 2015-05 |
1,516 | 8,073 | 2015-06 |
1,782 | 9,855 | 2015-07 |
1,624 | 11,479 | 2015-08 |
1,686 | 13,165 | 2015-09 |
1,170 | 14,335 | 2015-10 |
1,183 | 15,518 | 2015-11 |
936 | 16,454 | 2015-12 |
1,197 | 1,197 | 2016-01 |
1,203 | 2,400 | 2016-02 |
1,253 | 3,653 | 2016-03 |
994 | 4,647 | 2016-04 |
1,054 | 5,701 | 2016-05 |
1,159 | 6,860 | 2016-06 |
1,017 | 7,877 | 2016-07 |
856 | 8,733 | 2016-08 |
973 | 9,706 | 2016-09 |
1,884 | 11,590 | 2016-10 |
1,804 | 13,394 | 2016-11 |
944 | 14,338 | 2016-12 |
1,314 | 1,314 | 2017-01 |
1,514 | 2,828 | 2017-02 |
1,421 | 4,249 | 2017-03 |
1,424 | 5,673 | 2017-04 |
1,251 | 6,924 | 2017-05 |
1,300 | 8,224 | 2017-06 |
949 | 9,173 | 2017-07 |
664 | 9,837 | 2017-08 |
109 | 9,946 | 2017-09 |
As you may see, this function is working well until 2016-12, but seems starting from 2017-01, it started over...
below is how i set up this aggregated measure in VA:
CumulativePeriod(_Sum_, Frequency, Repair Date, _ByMonth_, _Inferred_, 0, _Full_, )
would it be possible to help me pointing out where i did wrong? or some reason outside my control?
Thanks a lot in advance.
Hello,
CumulativePeriod creates a cumulative total for each iteration of a periodic interval. In your expression,
CumulativePeriod(_Sum_, Frequency, Repair Date, _ByMonth_, _Inferred_, 0, _Full_, )
The bolded parameter specifies that the interval is inferred from the data. The largest interval that is used in VA is a year. So it is expected that the total starts over at the start of the calendar year.
If I understand correctly, you want to create a running total by month, that never starts over. Is that right? I believe the best way to accomplish this is through the SAS or SQL syntax in the data query that loads your table. This syntax is not my expertise but you might have some luck if you search for "running total".
I hope this helps,
Sam
Hello,
CumulativePeriod creates a cumulative total for each iteration of a periodic interval. In your expression,
CumulativePeriod(_Sum_, Frequency, Repair Date, _ByMonth_, _Inferred_, 0, _Full_, )
The bolded parameter specifies that the interval is inferred from the data. The largest interval that is used in VA is a year. So it is expected that the total starts over at the start of the calendar year.
If I understand correctly, you want to create a running total by month, that never starts over. Is that right? I believe the best way to accomplish this is through the SAS or SQL syntax in the data query that loads your table. This syntax is not my expertise but you might have some luck if you search for "running total".
I hope this helps,
Sam
Thank you, Sam. then i will have to go with different approach.
It should be fairly straightforward to make the needed syntax. Good luck!
I wanted to let you know, we plan to make this type of calculation much easier in the 8.2 release. As currently planned, you will be able to create this calculation as a right-click selection from the Data pane. There is also a new aggregated operator for the expression builder to make it possible.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.