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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

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

3 REPLIES 3
Sam_SAS
SAS Employee

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
Calcite | Level 5

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

Sam_SAS
SAS Employee

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

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.

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