topic Re: Pivot Table - Sorting with Totals / Multiple dimensions and Column dimensions in App Development

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

JPT — Tue, 30 Nov 2021 18:17:33 GMT

Hello,

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

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

brunobertels — Fri, 25 Jun 2021 13:35:53 GMT

Is this order you want :

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 — Mon, 28 Jun 2021 10:33:05 GMT

Thank you but we are still having issues:

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 — Wed, 30 Jun 2021 14:59:04 GMT

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)

alanwong1178 — Wed, 20 Oct 2021 08:11:30 GMT

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 — Thu, 21 Oct 2021 03:13:04 GMT

any update?