strange result when using CumulativePeriod in VA

[xiaoyao026]

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.

[Sam_SAS · Accepted solution]

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

[xiaoyao026]

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

[Sam_SAS]

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.