topic Re: Pivot Sorting in QlikView

Pivot Sorting

andrea90casa — Sat, 22 Jul 2017 11:59:26 GMT

Hi at all,

I have this situation,

I have a Pivot Table with three dimension: Month, Category, Product Name.

I would like to sort the Pivot in two way at the same time:

First sort by month (and I can do it), the second one by Category. This last generated an issue, in fact, as in the picture bleow

For "gennaio" everything is ok, but for "febbraio" Cat_2 is below Cat_3 and Cat_4 and this is not correct.

Correct order should be

Cat_2 73

Cat_4 67

Cat_3 33

Anyone can help me with this issue?

I thank in advance for your help.

Andrea

Re: Pivot Sorting

effinty2112 — Sun, 23 Jul 2017 09:50:23 GMT

Hi Andrea,

In the sort tab of the pivot table sort the Category dimension by this expression:

Match(Category,'Cat_2', 'Cat_4','Cat_3')

Regards

Andrew

Re: Pivot Sorting

andrea90casa — Sun, 23 Jul 2017 10:42:32 GMT

Hi Andrew,

Thank you for your help.

But if there is a situation with a lot of category and for twelve months how can I create a dynamic sorting. Is there the possibility to do it?

Andrea

Re: Pivot Sorting

Or — Sun, 23 Jul 2017 12:25:02 GMT

Your example does not appear to fit your request:

* If you want to sort by Category as you stated, the correct order would be Cat_1, Cat_2, Cat_3. This is trivial to accomplish - simply sort Category by A->Z.

* If you want to sort by Y-value - Sum(Amount) - based on the Category dimension, this gets pretty tricky. Have a look at this document which covers the way to achieve Y-value sorting by multiple dimensions in a pivot table:

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

Re: Pivot Sorting

andrea90casa — Sun, 23 Jul 2017 12:32:48 GMT

Hi Shoham

I would like to order the category for sum(amount) but inside each month. Now it is like it is order by sum(amount) but without considering the first dimension (month).

I will try to look at that document. Thanks

Andrea

Re: Pivot Sorting

effinty2112 — Mon, 24 Jul 2017 09:07:45 GMT

Andrea,

I totally misread your request and told you a lot of rubbish!

I going to have a cup of coffee then I may be of help.

Cheers

Andrew

Re: Pivot Sorting

effinty2112 — Mon, 24 Jul 2017 09:12:55 GMT

Hi Andrea,

Have you tried sorting Category by the expression sum(Amount) Ascending?

Andrew

Re: Pivot Sorting

andrea90casa — Mon, 24 Jul 2017 10:49:32 GMT

Hi Andrew

The problem is when I try to order by sum(amount) because qlik don't consider the only category in each month but any category.

So the correct step shuould be:

1) Order Month by name,

2) In each months consider only the category inside

3) Sorting those categories by sum(amount)

But Qlik doesn't do the second step because it's considers the entire set of categories.

So if I order category by sum(amount) the problem remains.

Andrea