🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

## strange result when using CumulativePeriod in VA

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: strange result when using CumulativePeriod in VA

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

3 REPLIES 3
SAS Super FREQ

## Re: strange result when using CumulativePeriod in VA

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

Calcite | Level 5

## Re: strange result when using CumulativePeriod in VA

Thank you, Sam.  then i will have to go with different approach.

SAS Super FREQ

## Re: strange result when using CumulativePeriod in VA

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.

Discussion stats
• 3 replies
• 1336 views
• 2 likes
• 2 in conversation