Customizing Pivot Table columns

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?

Best Answer

  • DavidChurchman
    Answer ✓

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DavidChurchman
    Answer ✓

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • 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?

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

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