% Change Columns in Pivot Table

akeating
akeating Contributor

Hi Dojo Community! 

My team is trying to create a card for a customer that allows them to select a range of years and/or months to compare (they will most likely view one month at a time), apply additional filters as needed, and drill into the table. 

The card should be structured like the screenshot below. There should be a % change column where each red arrow is. Note that this would always be a comparison to the previous year; the customer wouldn't be able to pick, for example, 2018 and 2022.

Does anyone know of a way to accomplish this without dataflows? If we can do it with a dataflow and still have the flexibility we need in filtering and drilling, that will work too.

Thanks!

**Was this post helpful? Click Agree, Like, or Awesome below.**
**Did this solve your problem? Accept it as a solution!**

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @akeating

    I'd recommend you reformat your data in a friendlier way to allow you to more easily calculate Period over Period difference. I've done several write ups on this in the past with step by step instructions on how to accomplish this: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

    This will allow you to easily calculate the year over year difference between the two years in your pivot table.

    Essentially once you get your dataset configured you can use a beast mode to calculate this year, last year and the year over year % change:

    (
      SUM(CASE WHEN `Period Type` = 'Current' THEN `random_number` END) -- This Year
      -
      SUM(CASE WHEN `Period Type` = 'Last Year' THEN `random_number` END)  -- Last Year
    
      )
      /
      (
        SUM(CASE WHEN `Period Type` = 'Last Year' THEN `random_number` END)
       )
    



    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @akeating

    I'd recommend you reformat your data in a friendlier way to allow you to more easily calculate Period over Period difference. I've done several write ups on this in the past with step by step instructions on how to accomplish this: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

    This will allow you to easily calculate the year over year difference between the two years in your pivot table.

    Essentially once you get your dataset configured you can use a beast mode to calculate this year, last year and the year over year % change:

    (
      SUM(CASE WHEN `Period Type` = 'Current' THEN `random_number` END) -- This Year
      -
      SUM(CASE WHEN `Period Type` = 'Last Year' THEN `random_number` END)  -- Last Year
    
      )
      /
      (
        SUM(CASE WHEN `Period Type` = 'Last Year' THEN `random_number` END)
       )
    



    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**