Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

strange result when using CumulativePeriod in VA

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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:

 

Frequencycummlated number of claimsRepair Date
43432014-02
13562014-03
701262014-04
1142402014-05
3035432014-06
776202014-07
1828022014-08
4961,2982014-09
5771,8752014-10
4062,2812014-11
5622,8432014-12
1,2661,2662015-01
1,0082,2742015-02
1,2633,5372015-03
8944,4312015-04
2,1266,5572015-05
1,5168,0732015-06
1,7829,8552015-07
1,62411,4792015-08
1,68613,1652015-09
1,17014,3352015-10
1,18315,5182015-11
93616,4542015-12
1,1971,1972016-01
1,2032,4002016-02
1,2533,6532016-03
9944,6472016-04
1,0545,7012016-05
1,1596,8602016-06
1,0177,8772016-07
8568,7332016-08
9739,7062016-09
1,88411,5902016-10
1,80413,3942016-11
94414,3382016-12
1,3141,3142017-01
1,5142,8282017-02
1,4214,2492017-03
1,4245,6732017-04
1,2516,9242017-05
1,3008,2242017-06
9499,1732017-07
6649,8372017-08
1099,9462017-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.

 

 


Accepted Solutions
Solution
‎10-08-2017 03:28 AM
SAS Super FREQ
Posts: 322

Re: strange result when using CumulativePeriod in VA

Posted in reply to xiaoyao026

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

View solution in original post


All Replies
Solution
‎10-08-2017 03:28 AM
SAS Super FREQ
Posts: 322

Re: strange result when using CumulativePeriod in VA

Posted in reply to xiaoyao026

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

Occasional Contributor
Posts: 19

Re: strange result when using CumulativePeriod in VA

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

SAS Super FREQ
Posts: 322

Re: strange result when using CumulativePeriod in VA

[ Edited ]
Posted in reply to xiaoyao026

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 306 views
  • 1 like
  • 2 in conversation