BookmarkSubscribeRSS Feed
jrippberger
Fluorite | Level 6

Hi all,

is it possible to create a cumulative average for all past times?

I have only found the option to do this with relative dates. But if I have a dataset of multiple years I cannot put all the months in manually.

At each step it must include all previous values but not future ones. I can't aggregate on cell level because I cannot name a unique identifier.

 

Thank you very much!

 

The data set might look like this:

Date             Measure  Wanted
01/01/2016  0.15          0.15

01/02/2016  0.20          0.175 ((0.15+0.20)/2)

.

.

.

01/03/2017  0.05         Sum(Measure(where date is before current date))/n

3 REPLIES 3
PetriRoine
Pyrite | Level 9

Hello @jrippberger ,

 

Does this look something you are after? (Done in VA 8.5)

 

Comm_CumulativeSum.PNG

My source data had two columns: Date, Measure

I created a new measure Cumulative Sum by creating a Derived Item

  1. In the Data pane, right-click the Measure data item and selected New calculation.
  2. Give a sensible Name and specify the Type: Cumulative Total
  3. Click OK.

 

I hope this helps.

 

Best regards,

Petri

jrippberger
Fluorite | Level 6
Hi Petri,

thanks for your answer. That would indeed help very much since I would only have to divide this by the cumulative total of the count of events.
But I don't seem to have that option:
My VA does only display the following options for type (i translated them from german to english):
Difference to the same interval in the past
Difference to a interval in the past
Difference to the same interval in the past - percent
Difference to a interval in the past - percent
Percent of Sum - Counts
cumulative periodic value
growth comparison
cumulative year value
cumulative year growth.

So somehow VA will only let me cumulate my values by specific time intervals not "total".
Although it seems I only have access to the SAS release 7.5 which is unfortunate.

Best regards,
jripp
PetriRoine
Pyrite | Level 9

Visual Analytics 8.2 introduced new one-click calculations including (the one I used) cumulative total (which can work across any dimension), moving average, and data suppression (which obscures an aggregated measure value if detail measure values could be easily inferred when too few rows contribute to the aggregated measure value). Although version VA 7.5 inherited great things from VA on Viya, apparently these one-click calculations were not on the list. 

 

Best regards,

Petri

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 1022 views
  • 0 likes
  • 2 in conversation