Seeking Help for Calculating YoY Moving Average and Building Growth Hypothesis

Question

Hi Pigment Community!

I hope this message finds you well. I am currently working on building a table that will display my Year-over-Year(YoY) Moving Average(MA) of opportunities to customers. The ultimate goal is to use these calculations to create a growth hypothesis, which will then be employed for forecasting future opportunities and customers. I want to ensure that my YoY MA calculations are not only viewable at all levels of granularity but also correctly calculated for both granular and total values. I'm facing two main challenges that I'm seeking assistance with.

Problem 1: Granular and Total Value Calculation I want to create a calculation that accurately computes both granular and total values without the aggregation issues I'm currently experiencing.

Problem 2: Referencing YoY MA Values for Future Growth Hypothesis I aim to reference YoY MA values in future metrics for the growth hypothesis without being hindered by the limitations of calculated items and 'show value as' functions.

Once I calculate my YoY MA values I want to use them to populate a growth hypothesis, which will be used to calculate a forecast for future opportunities and customers, I mention this because I want to be able to reference my YoY MA calculations so that they can be used in future metrics when I build my forecast. In addition to being able to reference my values I also want to have the actual calculations at all levels of granularity. Meaning that I want to avoid Pigment from aggregating the most granular values to provide the totals. I am aware that I could calculate 1 metric per level of granularity, however I want to avoid this because it is not scalable.

Initially, I attempted to use a table, but I realized that calculated items and 'show value as' cannot be referenced, limiting the table's functionality for forecasting. Additionally, I encountered issues with using 'show value as' on a 'show value as,' preventing me from achieving the desired outcome.

Additionally, I created a metric which has the correct values at the most granular level but provides an aggregation for totals, rather than the actual calculation. The ideal solution would be to build a metric that can be referenced and has the actual calculated values at the total level, however I currently think the best solution would be to have a metric for my hypothesis and a table to visualize my hypothesis.

Screenshots of my model and more info below

YoY MA Metric (correct at granular, but aggregated at total)

With this metric I have the values at the granular level, however I only have aggregations at the total level. I expect that these values could be used for my growth hypothesis, however if I looked at total values pigment would aggregate and provide me with an outcome different to what I am looking for. How can I adjust this metric to have the calculated values at all levels of granularity, while still being able to reference it in the future?

YoY MA Table (unable to use ‘CY MA and PY MA to calculate YoY MA)

In this table I have included my base data required for my calculation. I have my ‘CY Cust MS’ which is the moving sum of a set period based on a variable ‘moving average’. ‘CY MA’ was calculated using the ‘show value as’ function where I duplicated my ‘CY Cust MS’ and then showed the value as a % of ‘CY Opp MS’. The same steps were carried out for ‘PY MA’ using its respective values. The issue I am having is that I want to now use my ‘CY MA’ and ‘PY MA’ to calculate my ‘YoY MA’, which I would calculate by doing: [ ( CY MA - PY MA) * 100 ]. By doing this I would be able to calculate my YoY MA and ensure that my total values would be the actual calculation, rather than an aggregation of the granular values. The problem is that I cannot reference ‘CY MA’ and ‘PY MA’ because there were calculated using ‘show value as’. What then would be a better alternative to calculate my values so that I can have the correct total values?

More info

‘Moving Sum”(MS) = sums data from a chosen period, makes use of ‘Moving average’ variable
‘Moving Average’(MA) = references a variable that sets the size of the moving average (insures both models have same calculation)
Filter[month.end date...] = lets me filter for only the data I want
switchover date, -2 = lets me set that values are being calculated for same period (can be confident values should be the same when testing)

Thank you in advance for your expertise and assistance!

-Darious

icon

Best answer by Issam Moalla 16 January 2024, 14:00

View original

Issam Moalla · Accepted Answer

Hi @darious,I went through the recreation of the described use case and indeed it is quite tricky.The limitation we are facing here is the unavailability of applying the function at the aggregated levels and this occurs especially when we are applying division between metrics.As you stated in your question, the possible solution for your two challengeswould be creating a metric for the hypothesisto reference it and a table with the “show value as” or Calculated Item features for display.As a possible workaround we would need to create the calculation in metrics : CY MA, PY MA and YoY MA (the columns suffixed by formulain the screenshot above) these metrics would be the onesyou would reference for you following hypothesis.For the display purposes, using “show value as” option works for the CY MA (SVA) and PY MA (SVA) only but the YoY MA (SVA) would display incorrect numbers at the aggregated level since it is applying the difference between CY MA (formula) and PY MA (formula) metrics which are outputting incorrect numbers at the aggregated levels.A possible workaround for this would consists in:1- Creating two metrics Cust MS and Opp MS with an adding an additional dimension to the current structure: CY/PY:Cust MS: contains the CY Cust MS allocated to the CY item and PY Cust MS allocated to the PY item'CY Cust MS'[BY: 'CY/PY'."CY"] + 'PY Cust MS'[BY: 'CY/PY'."PY"]Opp MS:contains the CY Opp MS allocated to the CY item and PY Opp MS allocated to the PY item'CY Opp MS'[BY: 'CY/PY'."CY"] + 'PY Opp MS'[BY: 'CY/PY'."PY"]2- Creating a table with the two metrics and adding the MA using the Cust MS the show value as % of Opp MS:3- Adding the YoY Calculated item on the CY/PY dimension and applying the difference optionBy applying this we would get the following result:4- As you see, the limitation of this option is having the two columns Cust MS and Opp M under YoY which are applying the difference CY - PY.If we hide them we would be left only with the MA calculation, since they are metrics and they will be hidden for all table:The solution would beediting the calculation for this metric option (when you right click on one of the cells of the metric) to No calculation:We will get the following result:As a final step applying the hide empty rows and columns will hide automatically these two columns.Hope this workaround helps as it is not quite straight forward.Issam