Creating a 'Difference from previous period' variable on an aggregated measure in SAS VA version 7.3

[msnel]

Hi,

I have a dataset containing 2 values per person per year: the distance they ran that year and the total time per year it took them. Furthermore I have their agegroup. In SAS VA I've created an aggregated measure: SpeedAv = Sum [_ByGroup_] ('distance'n) / Sum [_ByGroup_] ('time'n), to calculate the average speed per person, but also to be able to see this per agegroup per year. Now I want to create a variable (SpeedDiff) that gives me the differences in SpeedAv from year to year per agegroup. I thought I would be able to do so by right-clicking my aggregated SpeedAv-variable and choosing Create > Difference from Previous Period using Year, but when I right-click my aggregated measures this option isn't shown. Apparently SAS VA doesn't have this option for aggregated measures? Does anyone know a work-around to achieve my goal? Thanks in advance!

Dataset (or see the attached excel-file):

Person_ID AgeGroup Year Distance (km) Time (hr)
A 16-25 2017 100 7,5
A 16-25 2018 200 14,7
A 16-25 2019 250 18,1
B 16-25 2017 500 40
B 16-25 2018 400 34,5
B 16-25 2019 800 68,3
C 26-35 2017 1000 81
C 26-35 2018 1100 85
C 26-35 2019 1500 120
D 26-35 2017 50 3,5
D 26-35 2018 75 5,7
D 26-35 2019 60 4,7

Output I'm looking for (or see the attached excel-file):

AgeGroup Year Distance (km) Time (hr) SpeedAV (km/hr) SpeedDiff
16-25 2017 600 47,5 12,6
16-25 2018 600 49,2 12,2 -0,4
16-25 2019 1050 86,4 12,2 0,0
26-35 2017 1050 84,5 12,4
26-35 2018 1175 90,7 13,0 0,5
26-35 2019 1560 124,7 12,5 -0,4

[PetriRoine]

Hello @msnel ,

Yes, this is possible. The problem in your approach is that VA doesn't currently allow nested aggregated expressions i.e. aggregated operators inside aggregated operators. So we have to use an approach that avoids this.

VA 8.3 introduced capability to create an aggregated data source which allows us to create SpeedAv without using aggregated operators. 

Step A: Create aggregated data source

1. In the Data pane, select a data source.
2. Click Action icon , and then select New data from aggregation of data-source. The New Aggregated Data window is displayed.
3. Select the data items AgeGroup, Year, Distance, Time to add to the aggregated data source in the Available items list. Then, click +> to move it to the Selected items list.
4. (Optional) Change the name of the aggregated data source in the Name field.
5. Click Ok. The new aggregated data source is available in the Data pane.

Step B: Create SpeedDiff

1. Create a new Calculated Item
   SpeedDiff = RelativePeriod(_Sum_, 'SpeedAv'n, _IgnoreAllTimeFrameFilters_,
   'Year'n, _Inferred_, 0, _Full_, {Date}) - RelativePeriod(_Sum_,
   'SpeedAv'n, _IgnoreAllTimeFrameFilters_, 'Year'n, _Inferred_, -1,
   _Full_, {Date})

Congrats, you are done 😃 Now just use your newly create aggregated measure SpeedDiff in List Table.

I hope this helps!

Best regards,

Petri

[msnel]

Hi Petri,

thank you for your quick reply. Does your answer apply to VA version 8.3 or also to 7.3 which I'm using? I can't seem to find the New data from aggregation of data-source option, you're talking about. If your solution doesn't apply to 7.3, would you know a work-around that works in version 7.3?

Thanks in advance!

Kind regards, Mark

[PetriRoine]

Sorry, this only applies to VA 8.3 and newer versions. I can't come up with any other means to achieve this in VA 7.3 than calculating SpeedAv to data before loading to LASR. VA 7.3 has periodic operator RelativePeriod so the end of the calculation should be doable. 

Best regards,

Petri

[msnel]

The reason I want to do this in VA and not calculate the SpeedAv before loading to LASR, is that I want users of my dashboard to be able to manually create categories in VA for which the SpeedAv and the changes in SpeedAv should be calculated. But perhaps I have to accept that this isn't possible in version 7.3. Thanks anyway!

[sharon8]

Hello,

I have a dataset that needs the same type of calculations applied as you demonstrated below. However, I need to take it a step further. After getting the difference,  how would you sum SpeedDiff, grouped by AgeGroup? I tried to create a new calculation to sum up the SpeedDiff by AgeGroup but got the dreaded error 'Nested aggregated expressions are not supported'. Thank you in advance for your help