Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

10 Replies
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

Dim1Dim2Dim 3Sales
ARX100
ASY200
ASZ300
BKF50
BKG75
BLH100
CMQ125

But if you have something like

Dim1Dim2Dim 3Sales
ARX550
ASY200
ASZ300
BRX50
BSG75
BSZ100
CMZ125

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Creator
Creator

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

Osvaldo Macoritto
SKI Tecnología
samuel_lin
Creator
Creator

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
Contributor
Contributor

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)

Capturar.PNG

thanks,

BR,

Martina

Tatynout
Contributor III
Contributor III

Hi Martina,

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

Please help.

 

Thanks.

Tatiana

cg2
Contributor
Contributor

Thank you
MChapp
Contributor
Contributor

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.