BookmarkSubscribeRSS Feed
vula
Fluorite | Level 6

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

 

 

2 REPLIES 2
Sam_SAS
SAS Employee

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

 

 

chrisjab
Fluorite | Level 6

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 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2 replies
  • 1490 views
  • 0 likes
  • 3 in conversation