Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
JPT
Partner - Contributor II
Partner - Contributor II

Pivot Table - Sorting with Totals / Multiple dimensions and Column dimensions

Hello,

We are having issues sorting a Pivot table that contains Totals, multiple Row dimensions and Column dimensions:

pivotsort1.png

Anyone can help us along in sorting every dimension descending? I have attached a sample data qvf.

Labels (2)
5 Replies
brunobertels
Specialist III
Specialist III

Hi 

 

Is this order you want :

brunobertels_0-1624628040352.png

then if so , 

then in order panel menu 

under continent add 

rank(aggr(Sum( [Amount]),Continent))

under country add 

rank(aggr(Sum( [Amount]),country))

under client add 

rank(aggr(Sum( [Amount]),client))

 

order will be defined by total column 

 

hope it helps

JPT
Partner - Contributor II
Partner - Contributor II
Author

Thank you but we are still having issues:

pivotsort2.png

 

As you can see the place where we have a - / missing value somehow break the sorting within the Pivot table? Any idea? I have attached a new qvf.

brunobertels
Specialist III
Specialist III

Hi 

See app upload 

Pivot table is sorting by sum(amoun) ascending on all dimension using calculating dimension and sort by expression 

Dimension : 

continent :

=
aggr(nodistinct
aggr(rank(-sum(Amount)),Continent),Continent)&' '& Continent

 

Country :

=
aggr(nodistinct
aggr(rank(-sum(Amount)),Continent,Country),Continent,Country)&' '& Country

Client :

=
aggr(rank(-sum(Amount)),Continent,Country,Client) &'-'& Client

 

then in the sorting panel sort by expression 

Continent =

aggr(
aggr(rank(-sum( {$<Mix=>} Amount)),Continent),Continent)

 

Country = 

aggr(nodistinct<Mix>
aggr(rank(-sum( {$<Mix=>} Amount)),Continent,Country),Continent,Country)

 

client 

aggr(
nodistinct
rank(- sum({<Mix=>} Amount),4),Country,Client)

 

brunobertels_0-1625065136928.png

 

alanwong1178
Contributor III
Contributor III

Rank(aggr) works for total column but it does not work with lowest level. In your case , I would like to sort client level as well. 

It is kind of like random sorting in client level even i added rank(aggr(Sum( [Amount]),client)). Any solution for that?

alanwong1178_0-1634717433682.png

 

alanwong1178
Contributor III
Contributor III

any update?