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

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.

Accepted answers

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

All comments

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!

usman.zia

Hi,

Always happy to help!

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

patcharavtk

Hi Usman,

This works flawlessly. Thank you very much!

Quick Links

Can you help?

Unanswered questions

Poll: Should I have a single model or two separate models?
Hi Anaplanners, I am hoping to collect some feedbacks from you on an ongoing discussion I've been having with my stakeholders. My organization currently have 1 TQP model, which is focused on keep the model small and agile, and quick enough for about 200 managers to use to plan out their territories within a 5 weeks…
Error "No current transaction manager" during data import
Hi Community, I ran into an error while manually importing a CSV file into a module in Anaplan. The error message displayed is: "No current transaction manager" Has anyone experienced this issue before? Thanks in advance for your help!
Need Help: Import Updates Only Specific Combinations Without Erasing Others
Hi Anaplan Community, I'm facing an issue while importing data (text details) into a module that has two dimensions: "Unique Line ID" and "Organization x Site". The source file is based on a numbered list. It includes text-formatted columns (line items) that contain the names of the "Unique Line ID" and "Organization x…