Is it possible to sort measure in pivot table using Qlik Sense?

[#1]

Using Qlik Sense, would it be possible to sort the data in a pivot table?

such as sorting the Sales column in the pivot table on the help page:

https://help.qlik.com/sense/en-us/online/Subsystems/Hub/Content/Visualizations/PivotTable/PivotTable...

Thanks,

Ping

[Gysbert_Wassenaar]

Yes, that's possible. It depends on your data what kind of trickery you need to resort to. It might be enough to set the sorting of each dimension to Custom, enable the Sort by expression option and use sum(Sales) as expression. That works for data sets like

Dim1	Dim2	Dim 3	Sales
A	R	X	100
A	S	Y	200
A	S	Z	300
B	K	F	50
B	K	G	75
B	L	H	100
C	M	Q	125

But if you have something like

Dim1	Dim2	Dim 3	Sales
A	R	X	550
A	S	Y	200
A	S	Z	300
B	R	X	50
B	S	G	75
B	S	Z	100
C	M	Z	125

then you probably have to resort to calculated dimensions like dual(Dim2, aggr(sum(Sales),Dim1,Dim2) for Dim2. Then you can sort that calculated dimension with Sort Numerically.

Unfortunately the pivot table has no Sort by Y-value like previous versions of Qlikview had.

[#3]

Thank you Gysbert.

How did you make the headers click-able to sort? I see they are clickable in your tables but the headers are not clickable in the pivot table created using Qlik Sense Pivot table visualization.

[colinodonnel]

Dear Gysbert,

Thank you for this solution. i.e. dual(Dim2, aggr(sum(Sales),Dim1,Dim2) for Dim2.

It is the only solution I have found that will sort a Pivot table by measure when it has more than one row.

This should be shared and put into the Qlik Sense Hall of Fame.

Cheers,

Colin

[Osvaldo_Macoritto]

excellent, works with more than 1 dimension previous to I want to sort and even with several metrics

[samuel_lin]

Hi gwassenaar‌,

What if my dimension 2 is a variable such as:

=$(Pkci(Match($vPNview), 1, 2), 'Dim2_1', 'Dim2_2')),

how should I apply your solution?

Thanks,

[yurgelmartina]

Hello,

I have a problem in my pivot table, that i am trying to sort it for thi measure. When I select the one dimention it is ok but then it is lost its formats.

Sum({$<Date = {'12/05/2018'},Type = {'2018'}  >} VendaFin)-Sum({$<Date = {'12/05/2018'},Type = {'2017'}  >} VendaFin)

thanks,

BR,

Martina

[Tatynout]

Hi Martina,

I have the same problem did you please find a solution to this? 

Please help.

Thanks.

Tatiana

[cg2]

Thank you

[MChapp]

Under sorting section, select your primary sort dimension and then make it a custom sort. Choose descending or ascending . Then build an expression to sum your measure, like sum(revenue), and this will sort your results.