SAS VA Variance and YOY calculations .

[vula]

Hi Techies,

Can anyone give me the solution to one of my requirement that i am facing in financial domain in calculating variance and YOY, YTD report in SAS VA.

My DATASET contains : Country(char) Rev(INT), SEG(Char), Prod(Char), Reporting Month ( 201501,201502..201610), Dom(INT) etc as variables.

Now i need to create summary cross table with variance values on each quarter. Also i had report filters for SEG and Prod.

please help me if it is possible in SAS VA. If so guide me on this item also what is the formula to create variance in SAS VA.

REV(INT) variable values are used in creating vaariance.

Quarter	Q1'16	Q2'16	Q1'15	Q2'15	Q1'16 vs Q1'15
Country	REV	DOM	REV	DOM	REV	DOM	REV	DOM	Var$/REV	Var%/REV
A	491	772	513	784	491	772	491	772	(22)	-4%
B	65	83	72	88	65	83	65	83	(7)	-9%
C	42	96	43	112	42	96	42	96	(1)	-2%
D	14	121	19	153	14	121	14	121	(5)	-25%
E	30	49	31	50	30	49	30	49	(1)	-3%

Also How to calculate YOY values for the same.

EX. This Values are measured using REV(INT) variable.

Q1'15	Q1'16	Q4'15	YoY %
738	736	740	100%

Q4'15	Q1'16	YoY $	YoY %
740	736	-4	-1%

What is the formula for YOY in SAS VA

What is the much difference between YOY calculation and Variance calculation on Same variable? What results we can see.

Any solution on these will help me a lot.

Thanks

[Sam_SAS]

Hello vula,

I believe that creating an aggregated measure with periodic operators should work for what you want.

The periodic operators are documented here:

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

The ParallelPeriod operator can be used to calculate YOY and the CumulativePeriod operator can be used to calculate YTD. 

So to get your Var$/REV you might have something like this:

Period(_Sum_, 'Rev'n, 'Quarter'n, _ByQuarter_) - ParallelPeriod(_Sum_, 'Rev'n, _ByQuarter_, _ByYear_, -1, _Full_)

In the code above, Period() returns the value for the current quarter and ParallelPeriod() returns the value of the same quarter in the previous year.

Let us know if that helps!

Thanks,

Sam

[vula]

Hi Sam,

I would like to thank you for your response..

i want some more inputs from you.

1. Is this is possible that i can do in cross tab as the diagram shown above like

In the cross table measures i am just creating  variance only for INT REV

I had tried but i am not able to show tabular format in the below style. But users want to see in the below style as this report style was taken from excel. If this style is not possible in SAS VA then how can we explain end user and aslo recommend the best choice. 

Any help sam.

                          Q1'16                Q4'15               Q3'15                  Q2'15            Q1'15             Q1'16vs Q1'15         Q1'16 vs Q4'15

Country     INT REV  D REV   Int REV  D REV   Int REV  D REV  Int REV D REV Int REV D REV      Var$       Var%       Var$     Var% 

  A                 20          25           15        10           50         5            35        20       30         30           -10         %              10      %

  B

  C

Thanks

[Sam_SAS]

Hello, can you show a screen capture of the result you are getting and the format that you want to achieve (from Excel)? This would help us to help you.

Thanks,
Sam

[vula]

Hi sam 

The above may not be visible properly or you.

Hi Sam,

I would like to thank you for your response..

i want some more inputs from you.

1. Is this is possible that i can do in cross tab as the diagram shown above like

In the cross table measures i am just creating  variance only for  R

I had tried but i am not able to show tabular format in the below style. But users want to see in the below style as this report style was taken from excel. If this style is not possible in SAS VA then how can we explain end user and aslo recommend the best choice. 

Any help sam.

                 Q2'16   Q1'16 Q4'15 Q3'15  Q2'15  Q1'15'  Q2'16vs Q2'15   Q1'16 vs Q1'15  Q2'16 vs Q1'16

Country      R  D    R  D    R  D   R   D    R  D      R   D   Var$       Var%       Var$     Var%    Var$     Var%

  A             20 25  15 10   50 5   35 20  30 30    10  20     -10          %           5        %           5       %

  B

  C

Also please help me on YOY in list table like

  Q1'16   Q1'15  Q4'15  YOY$   YOY%

    10         50        15       40         %

Thanks

[Sam_SAS]

I'm not sure whether you tried to attach an image. I don't see any images.

It would be very helpful to see images of the results you want and the results you are getting. You might want to use "dummy" data for this, or edit the image to hide the data values.

[vula]

Hi Sam,

Thank you your support.

I had attached the excel sheet current format in which the report looks like. They want to implement same kind of view in SAS VA. So is it possible to create variance columns seperately in Cross tab as attached. If not what is the best way.

For YTD i will use slider and put cycle data date dimension so that for each sliding the values can change accordingly, Correct me i my assumption was wrong.

Thanks

[Sam_SAS]

Thanks very much for providing the spreadsheet, I think I see the issue now.

What we can do in VA is to make a crosstab like this:

In this format, each value of VAR is the difference between the current quarter and the previous one, and each value of YOY is the difference between the current quarter and the same quarter of the previous year.

You can actually create the aggregated items automatically by right-clicking a measure and selecting Create > Difference from Previous Period or Create > Year over Year Growth. Similarly, you can create Year to Date values in this way.

However, this doesn't give a separate heading on the crosstab for each pairing of quarter values. If that is the only acceptable format, then I believe you could create those columns as part of your data query.

I'm not quite sure what you mean about a slider for YTD.

[vula]

Hi Sam ,

Thank you so much. The answer that you had provided gives me lot of solutions.

So can you please give me the formulas on how to create Variance(Var$), Variance(Var%), YOY($), YOY(%).

YTD- This is the new requiremnt that user is asking to implement in  the report. So i just want to confirm about the possibility of implementing this with slider using date data item, If you want to suggent anything on how to implement this please let me know. YTD needs to work on all the sections.

Thanks

[vula]

Hi Sam,

In the screen shot is it the variance for  only prevoius quarter, if so i have requiremnt for both previous year same quarter also same year previous quarter.

So please provide me the formulas for both in calculating variance. It will be great heelp i i can screenshot for the same

Thank you so much or your solutions.

I

[Sam_SAS · Accepted solution]

I'm glad that my post was helpful.

As I mentioned before, there are shortcuts in the user interface that you can use to create these aggegations automatically.

On the Data pane, if you right-click your Rev variable, you will see a menu option to "Create." You can choose from a number of different calculations, which are documented here:

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

I think that for Var$ you want "difference from previous period". For Var% you want "percent difference from previous period." For YOY$ you want "difference from previous parallel period" and for YOY% you want "year over year growth".

These selections will generate aggregated items whose expressions use code like this:

Period(_Sum_, 'Revenue'n, 'Date'n, _ByQuarter_) - ParallelPeriod(_Sum_, 'Revenue'n, 'Date'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})

But you probably don't need to worry about the expression code unless you want to do something more advanced.

I should mention that these calculations require a date data item. I'm not sure whether your Reporting Month variable is a basic character variable or if it is a proper date variable.

About using a slider with a date, you can use a slider with a date to filter your report, but you cannot use a date as a *parameter* in the current versions of Visual Analytics. I'm not sure if that was what you had in mind. 

[vula]

Thank You Sam, I will implement and want to ask you if i need more guidance.

I appreciate your time and thanks a lot.

[Sam_SAS]

I hope everything works well for you. Let us know if you have trouble.