Hi Techies,
Can anybody please guide us on about the formulas that we have to use in VA for calculating YTD, YOY and Variance values for measure items on Monthly and quarterly reports with Quarter and Month date items.
Thanks
Hello vula,
Can you be more specific about what you want?
Did you try creating the automatic aggregations from the Data panel?
Thanks,
Sam
Hi Sam,
I am getting more confused about creating YOY values and Variance values for the measure item using Date data item.
The problem i am facing was i am gettting same values for both calculations.
My user wants to see the YOY and also Variance values.
EX; In this table he wants see the revenue(INTL) by quarter and YOY value. ( i had data from only 1st quarter 2015)
Quarter 3rd quarter 2016 2nd quarter 2016 1st quarter 2016 4th quartet 2015 ....
INTL YOY Growth INTL YOY Growth INTL YOY Growth INTL YOY Growth
5899 20.82% 6543 12% 45678 19% 1265 -
Ex; 2 In second table he wants to see YTD values also variance(var) calculation; ( i had data from only 1st quarter 2015)
Quarter 3rd quarter 2016 2nd quarter 2016 1st quarter 2016 4th quartet 2015 ....
Country INTL YTD Var$ var% INTL YTD Var$ var% INTL YTD Var$ var% INTL YTD Var$ var%
USA 5899 xxxx xx x% 6543 xx xx x% 45678 xx xx x% 1265 xx - -
Now Please help me to develop the above two cross tables and what is the difference in calculating those.
Guide me with formulas and also it will be great help if you can provide me some examples.
Thanks
Hi Sam,
I am getting more confused about creating YOY values and Variance values for the measure item using Date data item.
The problem i am facing was i am gettting same values for both calculations.
My user wants to see the YOY and also Variance values.
EX; In this table he wants see the revenue(INTL) by quarter and YOY value. ( i had data from only 1st quarter 2015)
Quarter 3rd quarter 2016 2nd quarter 2016 1st quarter 2016 4th quartet 2015 ....
INTL YOY Growth INTL YOY Growth INTL YOY Growth INTL YOY Growth
5899 20.82% 6543 12% 45678 19% 1265 -
Ex; 2 In second table he wants to see YTD values also variance(var) calculation; ( i had data from only 1st quarter 2015)
Quarter 3rd quarter 2016 2nd quarter 2016 1st quarter 2016 4th quartet 2015 ....
Country INTL YTD Var$ var% INTL YTD Var$ var% INTL YTD Var$ var% INTL YTD Var$ var%
USA 5899 xxxx xx x% 6543 xx xx x% 45678 xx xx x% 1265 xx - -
Now Please help me to develop the above two cross tables and what is the difference in calculating those.
Guide me with formulas and also it will be great help if you can provide me some examples.
Thanks
Hi vula,
When you want to create Variance, is that the difference between 2016Q3 and 2015Q3 (the same quarter from last year), or the difference between 2016Q3 and 2016Q2 (the previous quarter)?
On the Data panel, if you right-click your INTL measure and select Create > Difference from Previous Parallel Period > Using Date, then that will calculate the difference between 2016Q3 and 2015Q3.
If you want to calculate the difference between 2016Q3 and 2016Q2, then select Create > Difference from Previous Period > Using Date.
You can use a similar "Percent Difference" selection to create the % variance.
YOY % Growth can be created in the same way by selecting Create > Year over Year Growth > Using Date
Are you able to create these calculations?
Thanks,
Sam
Hi sam,
Thank you so much for your reply.
I did the same calculations that you provided but i am just confusing in my case i am getting same value for both YOY growth and Var%.
Is this how it will calculate if so then why my user is asking YOY values and Var% values in two different tables. When i asked my friend he was telling me about varince calculation like calculting mean and doing the average etc... So VA is this happens in backgroung while i am using this formula.
I am sorry because i am not able to uplaod any document as this is not my personal laptop. i know you would have answered me very easily if i can upload my dashboard.
Is there any way that i can paste the dashboard in this mesage.
Currently i am calculating variance for difference between 2016Q3 and 2015Q3.
What is the YOY value in the dashboard that you had attached. Is this same as YTD value?
Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.