Customizing Pivot Table columns

kleespies1 · 2024-03-29T20:30:40+00:00

There was an error rendering this rich post.

Hi All,

I have created a pivot table from a data flow. The current setup is "Months" in the columns and "SUM of Sales", "SUM of LBs", and "SUM of Cases" in the Values. The output gives me columns of January Sales, January LBs, January Cases, February Sales, February LBs, February Cases, and so on. Is there a way to switch the columns and the values so that the output is January Sales, February Sales,…December Sales, January LBs, February LBs,….December LBs, January Cases, February Cases,…December Cases?

Find more posts tagged with

Accepted answers

DavidChurchman

So it sounds like your data is currently like this:

ID | Month | Sales | LBs | Cases

A | Jan | $5 | 2 | 7

To be able to do what you're describing, I think you'd have to unpivot (use the dynamic unpivot in MagicETL) so the structure is even longer. Like this:

ID | Metric | Month | Value

A | Sales | Jan | $5

A | LBs | Jan | 2

A | Cases | Jan | 7

Then in your pivot table you could set your Metric and Month as the columns, and the Value as the value.

All comments

DavidChurchman

So it sounds like your data is currently like this:

ID | Month | Sales | LBs | Cases

A | Jan | $5 | 2 | 7

To be able to do what you're describing, I think you'd have to unpivot (use the dynamic unpivot in MagicETL) so the structure is even longer. Like this:

ID | Metric | Month | Value

A | Sales | Jan | $5

A | LBs | Jan | 2

A | Cases | Jan | 7

Then in your pivot table you could set your Metric and Month as the columns, and the Value as the value.

ArborRose

Let's see if I have this correct….you have data that looks like this…

You create a report that looks like this…

But you want to unpivot the data so you can see All Sales, then all LBs, and all Cases.

We can take the sample2.csv data and run it through an ETL to create Sample3.csv.

Which gives us this..

And put that on a card…

Does that answer your question?

kleespies1

@DavidChurchman That is what I am looking for. Thank you!