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
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
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
MVP & Luminary
MVP & Luminary

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

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.

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

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

Osvaldo Macoritto
SKI Tecnología
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,

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

Contributor II
Contributor II

Hi Martina,

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

Please help.

 

Thanks.

Tatiana

Contributor
Contributor

Thank you
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.