Time Summary: MoM, QoQ, and YoY on the same line item

patcharavtk

Hi all,

 

I am trying to create a "% growth" line item, where this line should be able to represent MoM, QoQ, and YoY on the same line (with or without the use of working line items)

 

As time summary does not allow us to use OFFSET or PREVIOUS, what should be the best workaround for this?

 

(Note: with the creation of new working line item as previous, QoQ and YoY still does not work because it will take previous "month" instead of "quarter")

 

Practical example:
Value A:

M1: 9

M2: 8

M3: 7

Q1: 7

M4: 6

M5: 5

M6: 4

Q2: 4

Y2018: 30

Y2017: 20

 

In the line item that I would like to build, I want it to be as followings:

 

at %growth M3: = M3/M2-1 i.e. 7/8-1

%growth Q2: = Q2/Q1-1 i.e. 4/7-1

%growth Y2018 = Y2018/Y2017-1 i.e. 30/20-1, 

 

in the same %growth line item.

 

Does anyone has any suggestion to this? Any response is greatly appreciated. Thank you.

usman.zia

Hi, 

 

Always happy to help!

 

1. Yes the way to do this is for each line item that you want included such as 'Month %' , 'Quarter %' and 'Year %' make the summary for these items 'None'. Then create a new line item called 'Growth %' Then make the formula -  'Month %' + 'Quarter %' + 'Year %'  and set the summary for this to formula then this will bring in all of the growths in a line item series. 

2. Yes this will work for closing balance too. 

 

I hope this helps and if you have anymore questions please do let me know. 

 

Thanks

 

Usman 

usman.zia

Hi,

 

To calculate a previous period growth value for MoM, QoQ and YoY, the line items summary method for 'Value A' needs to be set to - 'Sum, Time: Sum'. This will then sum the line items 'Value A' for Quarter and Year totals. Then you must create a line item for bring back the previous value for 'Value A' for month, quarter and year such as 'Previous Value A Month', 'Previous Value A Quarter' and 'Previous Value A Year', then use the previous formula to bring in the previous value for 'Value A' for these. Then make the time dimension of 'Previous Value A Quarter' = Quarter and for 'Previous Value A Year' = Year. Then create a separate line item for each Growth % for Month, Quarter and Year and then use the formula 'Value A' / 'Previous Value A Month' - 1 and use the equivalent for quarter and year. This will then give you the correct growth values for comparing periods, quarters and years as separate line items and the values for the growth difference will show in the current period you are comparing.

 

I hope this helps! 

 

Please do let me know if you have any other questions or need further explanation. 

 

Kind Regards,

 

Usman 

patcharavtk

Hi Usman,

 

Thank you so much for your answer.

 

I have two follow up questions:

1.) Is there a way that we can combine all the calculated %growth (MoM, QoQ, YoY) into "one line"? i.e. Showing MoM in months and showing QoQ in quarters, and showing YoY in years.

2.) As the value in question has "closing balance" as the time summary, will your suggesed solution work in the same way?

 

Thanks again!

 

 

patcharavtk

Hi Usman,

 

This works flawlessly. Thank you very much!