Hi Techies,
Can any one provide solutions on variance calculations in SAS VA. Below is my data items and i need to show the basic table in cross tab.
Country _ Character | |||
Client - Character | |||
INTL - Numeric | |||
Product - Character | |||
Total - Numeric | |||
Segment - Character | |||
YRMONTH - EX - Date
|
In report variables = Quarter is custom category variable and DOM is calculated item i.e total-INTL.
Now i want to display the report in same table as shown in fig below.
Variance calculations are - Var $ = Q1'16(INTL)-Q1'15(INTL) Var % = Var $/Q1'15(INTL).
Q1'16 | Q1'15 | Q4'15 | Q1'16 v Q1'15 | Q1'16 v Q4'15 | |||||||||||||||
Dollar$m | INTL | DOM | Total | Int'l Mix | INTL | DOM | Total | Int'l Mix | INTL | DOM | Total | Int'l Mix | Var $ | Var % | Var $ | Var % | |||
688 | 2,545 | 3,234 | 21% | 693 | 2,593 | 3,286 | 21% | 683 | 2,663 | 3,347 | 20% | - 5 | -1% | 5 | 1% | ||||
country | 323 | 514 | 837 | 39% | 318 | 507 | 825 | 39% | 305 | 486 | 791 | 39% | 5 | 1% | 18 | 6% | |||
country | 31 | 43 | 74 | 42% | 33 | 45 | 78 | 42% | 32 | 43 | 75 | 42% | (2) | -5% | (1) | -2% | |||
country | 30 | 40 | 70 | 43% | 32 | 43 | 75 | 43% | 31 | 42 | 74 | 43% | (2) | -7% | (2) | -5% | |||
country | 30 | 48 | 78 | 39% | 33 | 52 | 85 | 39% | 32 | 52 | 84 | 39% | (3) | -8% | (2) | -7% | |||
country | 5 | 39 | 44 | 10% | 5 | 40 | 44 | 10% | 5 | 39 | 43 | 10% | (0) | -1% | 0 | 1% | |||
country | 28 | 56 | 84 | 33% | 24 | 48 | 72 | 33% | 28 | 56 | 83 | 33% | 4 | 18% | 0 | 1% | |||
country | 89 | 1,004 | 1,093 | 8% | 92 | 1,030 | 1,121 | 8% | 95 | 1,067 | 1,162 | 8% | (2) | -3% | (6) | -6% | |||
country | 33 | - | 33 | 100% | 37 | - | 37 | 100% | 35 | - | 35 | 100% | (4) | -10% | (2) | -6% | |||
country | 10 | 153 | 163 | 6% | 12 | 181 | 193 | 6% | 17 | 246 | 263 | 6% | (2) | -16% | (6) | -38% | |||
country | 15 | 105 | 120 | 12% | 15 | 106 | 120 | 12% | 14 | 102 | 116 | 12% | (0) | -1% | 0 | 3% | |||
country | 70 | 198 | 268 | 26% | 69 | 195 | 265 | 26% | 65 | 182 | 247 | 26% | 1 | 1% | 6 | 9% | |||
country | 20 | 84 | 104 | 19% | 20 | 85 | 105 | 19% | 20 | 87 | 107 | 19% | (0) | -1% | (1) | -3% | |||
country | 4 | 262 | 266 | 1% | 4 | 262 | 266 | 1% | 4 | 262 | 266 | 1% | 0 | 0% | 0 | 0% | |||
688 | 2,545 | 3,234 | 21% | 693 | 2,593 | 3,286 | 21% | 683 | 2,663 | 3,347 | 20% | (5) | -1% | 5 | 1% |
Also How to calculate YOY
Q1'15 | Q1'16 | Q4'15 | YoY % |
738 | 736 | 740 | 100% |
Any guidence from techies would really help me a lot
Thanks
Hello,
I'm not sure I understand what you want to do, but I will try to help.
You are going to want to create aggregated measures rather than calculated items.
I think for your "VAR $" you want to calculate the difference for each quarter's value versus the quarter from the previous year. You can use the ParallelPeriod operator to retrieve the value of the same quarter for the previous year.
So, here I am using Profit as the measure and Date is my date variable.
Sum [_ByGroup_] ('Profit'n) - ParallelPeriod(_Sum_, 'Profit'n, 'Date'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})
This compares each quarter's profit to the profit from the same quarter last year.
I'm not sure whether your Var % is supposed to compare the Var $ value for the current quarter or for the previous year's quarter.
For the current quarter you can just do,
Var $ / Sum [_ByGroup_] ('INTL'n)
For the previous year's quarter,
'Var $'n / ParallelPeriod(_Sum_, 'INTL'n, 'YRMONTH'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})
Is YOY different from your Var %? You should be able to create it using one of the periodic operators documented here:
Hopefully this will get you on the right track, even if the expressions are not perfect for what you want.
Sam
This approach works fine for a year over year. If I want to get a week over week view, what would be the best approach. Thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.