Pivot Sorting

[andrea90casa]

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

[effinty2112]

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

[andrea90casa]

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

[Or]

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)

[andrea90casa]

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

[effinty2112]

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

[effinty2112]

Hi Andrea,

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

Andrew

[andrea90casa]

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