Any chance I can add in a percent change over various fields in a pivot table via window funciton?

MattLynn · 2022-08-01T18:30:51+00:00

There was an error rendering this rich post.

I am doubtful that it is possible, but I'm holding out hope that somebody might have some insight. In excel I can take the values of two different cells per row to calculate a percent change, but in SQL I only know how to do this when it's isolated across a single dimension.

Below is a screenshot of two pivot tables with various fields. I can take any of those columns in its own card and generate a table with a percent change over two years. But I want to keep the dashboard concise and limit the cards so my current method is several small single-value cards that represent each of the field's percent change. I'm wondering if it is at all possible to work some window function magic that will take the table itself as the dataset and run a percent change across each row/column.

An ideal summarization might look something like this:

Any thoughts/ideas are welcomed!

Find more posts tagged with

Cards

Dashboard

Beast Mode

Formulas

Accepted answers

ST_Superman

I played around a bit with combining a multi-value gauge card with a flex table card:

I feel like that provides a pretty clean look.

All comments

ST_Superman

@MattLynn I think that you will be best served by splitting this table into multiple tables. One for case load by source, one for Resolution method, and one for Customer Experience.

I'm not sure how your data is structured, but I took a stab at the first table (case load by source). The part that I stumbled on here was that your summary value is the total case volume while the rest of the values are displayed as a percent of that total. I chose to include both on my table:

Here are some of the beastmodes that I wrote, but these may not work for your dataset. It might be easier if you shared a bit of the data behind this request so that someone can try to provide a more concrete solution.

2021 Qty

SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END)

Qty % Change

(SUM(CASE WHEN `Year`=2022 then `Quantity` ELSE 0 END) - SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END))
/
SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END)

2021 %

sum(case when `Year`=2021 then `Quantity` END) / sum(sum(case when `Year`=2021 then `Quantity` END)) over ()

% Change

((100*sum(case when `Year`=2022 then `Quantity` else 0 END) / sum(sum(case when `Year`=2022 then `Quantity` else 0 END)) over () )
 - (100*sum(case when `Year`=2021 then `Quantity` else 0 END) / sum(sum(case when `Year`=2021 then `Quantity` else 0 END)) over ())
)
/
(100*sum(case when `Year`=2021 then `Quantity` else 0 END) / sum(sum(case when `Year`=2021 then `Quantity` else 0 END)) over ())

I'm not sure how helpful this will be, as I probably do not have the same schema as your dataset, but I hope this helps.

RobSomers

@MattLynn You can also do this through and ETL if you don't care about filtering/drilling. You can just split your dataset and create a separate 'table' for each metric, and do the 2021 and 2022 values, and % Change for each one, and then append them all and create your card off the new dataset.

MattLynn

@ST_-Superman-_ Thanks for the detailed response! Your method is how I had it previously, but I wanted to opt for something more concise. I still have the percent changes in separate cards shown as single values which are about the cleanest I can get the dashboard to view without scrolling on most desktop/laptop devices.

I'd have to play around with the names to share some of the data behind the cards, but it's effectively various companies' cases along with survey stats for each specific case.

@RobSomers I'd be okay with that if it were only snapshots, but the teams that use this dashboard need to filter by their companies, employees, dates, and even products which I haven't added to this view yet. I wish I could run that whole process through a card when someone queries it in a specific way.

Thanks, both of you.

ST_Superman

I played around a bit with combining a multi-value gauge card with a flex table card:

I feel like that provides a pretty clean look.

MattLynn

@ST_-Superman-_ Yeah that is actually how I have it in another dashboard! It works great for that field, but the survey stats ones are fields of their own. I may end up transposing those into another dataset one day but it'll be duped across the other dimensions.