BookmarkSubscribeRSS Feed
qiaojinggg
Fluorite | Level 6

Hi, I'm trying to find the percentage difference of counts from previous period (eg previous year/quarter/month). I'm currently using the aggregated measure way in report designer where the percentage difference is easily generated by SAS VA. However, this approach will result in the first period percentage column in the crosstab to be empty (since there isn't any previous period). So for instance, the first period on my crosstab is 2011, the percentage difference between 2010 & 2011 will not be shown (refer to image) since 2010 is not in the crosstab and I'm not planning to display 2010 on the crosstab. However, I do have data for 2010. 

perc diff empty.png

The SQL code for the aggregated measure i'm currently using is 

 

( RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, 0, _Full_, {Date}) - RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, -1, _Full_, {Date}) ) / Abs(RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, -1, _Full_, {Date}))

 

Hence, I'm thinking if it's possible to make the percentage difference statis by defining it in the data query whereby I have a list of percentage difference generated for different period and just place it in the table.

 

However, I'm not sure if this is possible or how to go about doing it. 

 

I'm open to new ideas and suggestions. Any help will be greatly appreciated. 

 

Thank you very much!

1 REPLY 1
TedStolarczyk
SAS Employee

qiaojinggg:

 

Before I can possibly solve your questions, I need some clarification first.

 

For the 1st part of your question ... is this what you are after?

Wherever your Aggregated Measure formula results yield in . (aka dot, aka missing) … are you looking to hide the column(s)?

Meaning, are you just looking to get rid of the “Difference (YR)” 2011 column since it’s empty? Otherwise, the percentage difference calculation seems to work for you, is that correct?

 

For the 2nd part of your question ... are you asking if SAS Visual Data Builder would be better for your scenario vs. SAS Visual Analytics Designer?  If so, I'll need clarification for the 1st part of your question (see above), before I can attempt this one as well.

 

Sincerely,

Ted Stolarczyk, SAS Customer Loyalty team

 

 

SAS Innovate 2025: Call for Content

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 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 1 reply
  • 1249 views
  • 0 likes
  • 2 in conversation