Hi,
I am looking for someone helping me with an issue. I need to calculate the percentage difference from the previous parallel period (same month in the previous year) and from the previous period (previous month) for an aggregate measure that is calculated as following:
Distinct [_ByGroup_] ('ID'n)
ID is the identification code for unique client. And I also need this all to be parametrized. To sum up, right now in the report, I can choose a date (that is collected in the parameter Selected_date) and for that date I can see:
What I need to find and show is the percentage difference for both cases. Let’s focus on the difference from the previous month. Concerning to the % difference from the previous month, right now we have an aggregate measure calculated as follow:
( RelativePeriod(_DistinctCount_, 'ID'n, _ApplyAllFilters_, 'Order_date'n, _ByMonth_, 0, _Full_, {Date}) / RelativePeriod(_DistinctCount_, 'ID'n, _ApplyAllFilters_, 'Order_date'n, _ByMonth_, -1, _Full_, {Date}) ) - 1
Order_date is the date we need to take into consideration when counting, the date in which the order has taken place. And we also are applying the following filter directly in the report object:
IF ( 'Selected Measure'p = 'MTD' )
RETURN (
IF ( Month('Selected_date'p) = 1 )
RETURN ( ( ( 'Order_date'n >= DateFromMDY(12, 1, ( Year('Selected_date'p) - 1 )) ) AND ( 'Order_date'n <= DateFromMDY(12, DayOfMonth('Selected_date'p), ( Year('Selected_date'p) - 1 )) ) ) OR ( ( 'Order_date'n >= DateFromMDY(1, 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(1, DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) )
ELSE ( ( ( 'Order_date'n >= DateFromMDY(( Month('Selected_date'p) - 1 ), 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(( Month('Selected_date'p) - 1 ), DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) OR ( ( 'Order_date'n >= DateFromMDY(Month('Selected_date'p), 1, Year('Selected_date'p)) ) AND ( 'Order_date'n <= DateFromMDY(Month('Selected_date'p), DayOfMonth('Selected_date'p), Year('Selected_date'p)) ) ) ) )
ELSE ( 'Order_date'n > DateFromMDY(1, 1, 2999) )
But this measure is working only for the current month and it returns 100% if we select a date in March (month previous to the current one) and “missing” if we select a date in a month previous to March. Is there anyone who can help me in fixing it?
Thank you a lot and I am at disposal for any clarification!
Regards,
Giulia
Hi Giulia,
It sounds like you want to perform a nested aggregation (applying RelativePeriod() to an aggregated measure using Distinct()).
Visual Analytics does not support nested aggregations, so I think you would need to calculate your distinct count as part of your data query. Would this be possible?
Thanks,
Sam
Hi Sam,
Thank you for your reply.
Unfortunately, to work on the data query is not possible right now. Isn't there any shortcut or way to do it anyway?
Thank you again!
Giulia
The standard workaround is to modify the data query, but there may be other ways.
The post at the bottom of this thread might be helpful?
https://communities.sas.com/t5/SAS-Visual-Analytics/Aggregation-of-an-aggregation/m-p/312213/highlig...
(Actually no, I don't think it will help if you need Distinct Count.)
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 16. Read more here about why you should contribute and what is in it for you!
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.