YTD and YOY Calculations.

[vula]

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

[Sam_SAS]

Hello vula,

Can you be more specific about what you want?

Did you try creating the automatic aggregations from the Data panel?

Thanks,

Sam

[vula]

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

[vula]

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

[Sam_SAS]

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

[vula]

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

[Sam_SAS · Accepted solution]

Ahh, "YOY Growth" and "Percent Difference from Previous Parallel Period" are the same thing in these circumstances.

If you were looking at monthly values from quarter to quarter, the "Percent from Previous Parallel Period" calculation would be different.

FYI, in the data panel you can right-click an aggregated measure and select Edit Aggregated Item to see the expression code.

About screenshots, you can create a screenshot in VA by right-clicking the crosstab and selecting Export Image. Obviously though, you should take care not to reveal any data that is confidential to your company or client.

In my screenshot above, VAR is the difference from the previous quarter and YOY is the difference from the same quarter of last year.