BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vula
Fluorite | Level 6

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.

 

QuarterQ1'16Q2'16Q1'15Q2'15Q1'16 vs Q1'15
CountryREVDOMREVDOMREVDOMREVDOMVar$/REVVar%/REV
A491       772       513       784491       772491       772(22)-4%
B65         83         72         8865         8365         83(7)-9%
C42         96         43       11242         9642         96(1)-2%
D14       121         19       15314       12114       121(5)-25%
E30         4931         5030         4930         49(1)-3%

 

Also How to calculate YOY values for the same.

 

 

 

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

 

Q1'15Q1'16Q4'15YoY %
738736740100%

 

 

Q4'15Q1'16YoY $YoY %
740736-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

 

 

 

 

 

 

 

 

    
    
1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

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. 

View solution in original post

12 REPLIES 12
Sam_SAS
SAS Employee

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
Fluorite | Level 6

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
SAS Employee
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
Fluorite | Level 6

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
SAS Employee

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
Fluorite | Level 6

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
SAS Employee

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:

 

varyoy.png

 

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
Fluorite | Level 6

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
Fluorite | Level 6

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
SAS Employee

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
Fluorite | Level 6

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
SAS Employee

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

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