% DIFFERENCE FOR AN AGGREGATED MEASURE

[GiuliaM]

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:

1. The count from the beginning of the current month up to Selected_date
2. The count from the beginning of the same month in the previous year (up to the day of the month defined in Selected_date)
3. The count from the beginning of the previous month and up to the day of the month defined in Selected_date

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 

[Sam_SAS]

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

[GiuliaM]

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

[Sam_SAS]

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.)