MoM Variance in Pivot

Dverdon
Dverdon Member
in Charting

Hi All

is there a way to display the MoM Variance values in a pivot?

for context i have data per day for Company, Division, Subdivision hierarchy,

i can easily display the values at the end of the month in the hierarchy state but what i would like to do is calculate/display the monthly movement with the ability to drill down the hierarchy

i know there are various Period over period visuals but im yet to find one which solves


Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    @Dverdon As @MichelleH says, pivot tables won't allow you to calculate in between columns and a Mega Table with a Beast Mode might be your best bet, as you won't need to do much to transform your data prior, although you'll lose the drill down capabilities you have in a Pivot Table (in case that matters to your case).

    Another option would be to use an ETL to have the displaced value listed as well (Using DATE_ADD(`Date`,INTERVAL 1 MONTH)), there would be basically 2 ways to achieve this, either with APPEND or JOIN.

    The Append route is simpler although it will require you to store more rows, you'll need to identify the values with an additional column with either a value of 'Current' or 'Displaced' and then in your beast modes you'll use:

    Current value

    CASE WHEN `Identifier` = 'Current' THEN `Value` END

    Variance

    CASE WHEN `Identifier` = 'Current' THEN `Value` WHEN `Identifier` = 'Displaced' THEN -1*`Value` END

    And you'll add these to your Pivot Table as values, selecting SUM as the aggregation type (if you want % of change you'll need to do the aggregation and division as part of your beast mode).

    Keep in mind that when adding 1 month, any date that would fall outside the next month, becomes the last of the month instead (Jan 29, Jan 30, Jan 31 both become Feb 28/29 for example).

Answers

  • MichelleH
    MichelleH Coach

    @Dverdon Pivot Table charts don't have a native way to calculate the variance between dimensions (in this case months), so I typically calculate them using beast modes in a Mega Table. Can you show a mock-up of how you want the card to look?

  • Dverdon
    Dverdon Member

    @MichelleH Thank you, so something along the lines of the below - Figures at each level of the Hierarchy (company) showing values and monthly movements, ideally then drilling down the Hierarchy showing the breakdown by next layer (division)

    image.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    @Dverdon As @MichelleH says, pivot tables won't allow you to calculate in between columns and a Mega Table with a Beast Mode might be your best bet, as you won't need to do much to transform your data prior, although you'll lose the drill down capabilities you have in a Pivot Table (in case that matters to your case).

    Another option would be to use an ETL to have the displaced value listed as well (Using DATE_ADD(`Date`,INTERVAL 1 MONTH)), there would be basically 2 ways to achieve this, either with APPEND or JOIN.

    The Append route is simpler although it will require you to store more rows, you'll need to identify the values with an additional column with either a value of 'Current' or 'Displaced' and then in your beast modes you'll use:

    Current value

    CASE WHEN `Identifier` = 'Current' THEN `Value` END

    Variance

    CASE WHEN `Identifier` = 'Current' THEN `Value` WHEN `Identifier` = 'Displaced' THEN -1*`Value` END

    And you'll add these to your Pivot Table as values, selecting SUM as the aggregation type (if you want % of change you'll need to do the aggregation and division as part of your beast mode).

    Keep in mind that when adding 1 month, any date that would fall outside the next month, becomes the last of the month instead (Jan 29, Jan 30, Jan 31 both become Feb 28/29 for example).

Categories