Customizing Pivot Table columns

kleespies1
kleespies1 Member
in Charting

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
    DavidChurchman Coach
    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
    DavidChurchman Coach
    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.

  • ArborRose
    ArborRose Coach

    Let's see if I have this correctโ€ฆ.you have data that looks like thisโ€ฆ

    image.png

    You create a report that looks like thisโ€ฆ

    image.png

    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.

    image.png

    Which gives us this..

    image.png

    And put that on a cardโ€ฆ

    image.png

    Does that answer your question?

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

  • kleespies1
    kleespies1 Member

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

Categories