Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

satishsure
New Contributor

Pivotal table columns as rows (Not data wise)

I have pivotal table in qliksense. I need to convert as like below.

   

Quarter2017 Q 2
Quarterly SalesSalesCount Avg qtyUnits
1 - 10K112-45
10K - 50K551211
50K - 100K444055
100K - 250K3345511556
250K - 500K52663477
500K+2288-13

The Converted table should be like as below and attached image:

            Quarterly Sales      |Quarter | 2017 Q 2

-----------------------------------------------------------------

Sales     1 - 10K                                     11

               10K - 50K                                  55

                50K - 100K                              44

               100K - 250K                              33

               250K - 500K                               52

               500K+                                          22

Total                                                        217

--------------------------------------------------------------

Count       1 - 10K                                         2

               10K - 50K                                    12

               50K - 100K                                   4

               100K - 250K                                455

               250K - 500K                                  66

               500K+                                            88

             Total                                              627

-----------------------------------------------------------------

Avg qty          1 - 10K                                          -

                    10K - 50K                                    11

                    50K - 100K                                    0

                    100K - 250K                               11

                    250K - 500K                               34

                    500K+ -

               Total                                                56

-----------------------------------------------------------------

Units           1 - 10K                                         45

                    10K - 50K

                     50K - 100K                                   55

                    100K - 250K                                  556

                    250K - 500K                                  77

                    500K+                                           13

            Total                                                     746


Can we transpose the pivotal table column wise not data wise? as per above table

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivotal table columns as rows (Not data wise)

Is this what you want?

Capture.PNG

It was a lot simpler then I thought... just dragged the Measure to the rows sections and put it above test

6 Replies
MVP
MVP

Re: Pivotal table columns as rows (Not data wise)

Would you be able to share a sample or raw data for this?

satishsure
New Contributor

Re: Pivotal table columns as rows (Not data wise)

Hi Sunny,

I can not share the data..that why sample data added.Actually

Quarterly Sales,Quarter are Dimensions and

Sales, Count, Avg qty, Units are expressions

Quarterly Sales Expression as below to convert into intervals:

=IF(Aggr(Sum(SalesRaw), [Seller],[Quarter])>500000, Dual('500K+', 500),

IF(Aggr(Sum(SalesRaw), [Seller],[Quarter])>250000, Dual('250K - 500K', 250),

IF(Aggr(Sum(SalesRaw), [Seller],[Quarter])>100000, Dual('100K - 250K', 100),

IF(Aggr(Sum(SalesRaw), [Seller],[Quarter])>50000, Dual('50K - 100K', 50),

IF(Aggr(Sum(SalesRaw), [Seller],[Quarter])>10000, Dual('10K - 50K', 10),

Dual('1 - 10K',1))))))

--------------------------

MVP
MVP

Re: Pivotal table columns as rows (Not data wise)

Where is the sample data added? Are you talking about the pre-aggregated data that you have in your initial post? I am looking for raw data (mocked up data will work), but something I can use to recreate your expressions

satishsure
New Contributor

Re: Pivotal table columns as rows (Not data wise)

Hi Sunny..Thanks for the reply..Could you please use the below file and qvf..Need the help

MVP
MVP

Re: Pivotal table columns as rows (Not data wise)

Is this what you want?

Capture.PNG

It was a lot simpler then I thought... just dragged the Measure to the rows sections and put it above test

satishsure
New Contributor

Re: Pivotal table columns as rows (Not data wise)

Thank you..Sunny..:-)