BookmarkSubscribeRSS Feed
GiuliaM
Calcite | Level 5

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 

3 REPLIES 3
Sam_SAS
SAS Employee

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
Calcite | Level 5

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
SAS Employee

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

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

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