Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

SAS VA Variance calculations.

Reply
Contributor
Posts: 20

SAS VA Variance calculations.

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
2015-01
2015-02
2015-03

 

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'15Q1'16 v Q4'15
Dollar$mINTLDOMTotalInt'l Mix INTLDOMTotalInt'l Mix INTLDOMTotalInt'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           83739%            318           507           82539%            305           486           79139% 51%186%
country             31             43             7442%              33             45             7842%              32             43             7542% (2)-5%(1)-2%
country             30             40             7043%              32             43             7543%              31             42             7443% (2)-7%(2)-5%
country             30             48             7839%              33             52             8539%              32             52             8439% (3)-8%(2)-7%
country               5             39             4410%                5             40             4410%                5             39             4310% (0)-1%01%
country             28             56             8433%              24             48             7233%              28             56             8333% 418%01%
country             89       1,004       1,0938%              92       1,030       1,1218%              95       1,067       1,1628% (2)-3%(6)-6%
country             33               -               33100%              37               -               37100%              35               -               35100% (4)-10%(2)-6%
country             10           153           1636%              12           181           1936%              17           246           2636% (2)-16%(6)-38%
country             15           105           12012%              15           106           12012%              14           102           11612% (0)-1%03%
country             70           198           26826%              69           195           26526%              65           182           24726% 11%69%
country             20             84           10419%              20             85           10519%              20             87           10719% (0)-1%(1)-3%
country               4           262           2661%                4           262           2661%                4           262           2661% 00%00%
            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'15Q1'16Q4'15YoY %
738736740100%

 

 

Any guidence from techies would really help me a lot

 

Thanks

 

 

SAS Super FREQ
Posts: 290

Re: SAS VA Variance calculations.

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:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n1lxnqfip132can1hdzue...

 

Hopefully this will get you on the right track, even if the expressions are not perfect for what you want.

 

Sam

 

 

Contributor
Posts: 21

Re: SAS VA Variance calculations.

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 

Ask a Question
Discussion stats
  • 2 replies
  • 335 views
  • 0 likes
  • 3 in conversation