Display Latest & Avg Value of a variable calculated using Aggregated Periodic Operators in Key Value

saunvida — Fri, 27 Sep 2024 10:13:12 GMT

Hello SAS Community,

I’m working on Visual Analytics on SAS Viya 3.5 with monthly customer data that looks like this:

Period Product Active_Customers
Jan2004 Loan 10
Feb2004 Loan 15
... ... ...
Dec2012 Loan 22
Jan2004 Spend 19
... ... ...
Dec2012 Spend 41
Jan2004 Save 19
... ... ...
Dec2012 Save 41

I’ve added a product filter (dropdown list) and a time-slider to filter by 'Period' on the dashboard.

I have used Aggregated Periodic Operators (specifically the Relative Period operator) to calculate the 'Difference in Active Customers' for each period relative to the previous period.

I want to do two things:
1. Display the latest value of 'Difference in Active Customers' for the selected product and time range in a Key-Value.
2. Display the average value of the last 12 rows of 'Difference in Active Customers' in a separate Key-Value.

I’m able to display the latest 'Difference in Active Customers' in a list table by ranking 'Period' (converted to numeric using TreatAs operator) and selecting the top 1 row. However, this approach doesn’t seem to work for Key-Value, as it shows missing values. I have attached an image for reference.

For the average of the last 12 rows, I attempted to use the Avg operator on 'Difference in Active Customers' variable but received an error: "Nested Aggregated Expressions are not supported."

How can I correctly display these two calculated values of 'Difference in Active Customers' in a Key-Value?
Any suggestions on how to implement this would be greatly appreciated. Thanks in advance for your help!

topic Display Latest & Avg Value of a variable calculated using Aggregated Periodic Operators in Key Value in SAS Visual Analytics

Display Latest & Avg Value of a variable calculated using Aggregated Periodic Operators in Key Value