Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

baruchjacob
New Contributor III

Pivot Table Sorting - Qlik sense

Hi,

I Have ש pivot table with three dimensions and one expression,

the table is fully expanded and the third dimension shows only top 10.

I want that the third dimension will be in descending order by the expression.

any idea.

Thanks,

Jacob

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: Pivot Table Sorting - Qlik sense

Ok Jacob,

For the second and third dimension, please replace them with the following respectively:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)


That will make each value unique within each subgroup


I hope this helps,


Please try and let me know,


Cheers,


Luis

26 Replies
OmarBenSalem
Esteemed Contributor

Re: Pivot Table Sorting - Qlik sense

Go to the sort tab; go to ur 3rd dimension;

sort it by expression; tap the measure u're using and sort it as descending:

Capture.PNG

baruchjacob
New Contributor III

Re: Pivot Table Sorting - Qlik sense

Hi,

Thanks for the advise, unfortunately it isn't working.

luismadriz
Valued Contributor

Re: Pivot Table Sorting - Qlik sense

Hi Jacob,

If the values in your dimensions can appear in different groups then the solution that Omar is showing may need some tweaking. Can you share a bit more about it?

Cheers,

Luis

baruchjacob
New Contributor III

Re: Pivot Table Sorting - Qlik sense

Yes, The third dimension can appear more than once for example

Shop   Group  Item     Qty

A          I           X          54

                        Y          58    

                        Z          35

B          I           X          85

                        T           42

                         R          23

             II          S          55

                         M         22

                         L          15 



Thanks.

luismadriz
Valued Contributor

Re: Pivot Table Sorting - Qlik sense

Ok Jacob,

For the second and third dimension, please replace them with the following respectively:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)


That will make each value unique within each subgroup


I hope this helps,


Please try and let me know,


Cheers,


Luis

baruchjacob
New Contributor III

Re: Pivot Table Sorting - Qlik sense

Thanks, but still not working

you might forgot to mention: sum(qty) any where?

luismadriz
Valued Contributor

Re: Pivot Table Sorting - Qlik sense

Hi,

Yeap you're right. I'm assuming you're following Omar's recommendation (Sorting by expression with Sum, etc.)

It's the same that Omar said but the 2nd and 3rd dimensions on your Pivot Table needed to be changed

Please try and let me know

Cheers,

Luis

baruchjacob
New Contributor III

Re: Pivot Table Sorting - Qlik sense

Hi,

Tried without a success.

=AGGR(DUAL(Group,RowNo(sum(TOTAL Qty))),Shop,Group)

=AGGR(DUAL(Item,RowNo(sum(TOTAL Qty))),Shop,Group,Item)

luismadriz
Valued Contributor

Re: Pivot Table Sorting - Qlik sense

Hehe, not like that,

Is it possible for you to share data?

Community Browser