% Change Columns in Pivot Table

akeating

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!

GrantSmith

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)
   )