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.
Hi
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
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.
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)
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?
