Sort by expression in pivot table

[#1]

Hi,

I have a pivot table with 3 dimensions and 1 expression (sum of amount) and I want to sort this table by this expression, so I can see the largest amounts first.

I try to add this expression in Sort / Sort by expression field for dimensions, but it's not working.

BR, Andris

[#2]

A pivot table is by definition sorted by dimensions (first, second, ..)

Use table-style instead.

[#3]

In table I cannot show sum of amount for used dimensions.

[Anonymous · Accepted solution]

You could consider creating a table in the script part which contains the values you try to achieve with the expressions ... this way you can show the data in a table and you can sort on the data as you wish ...

[#5]

Well, this can be used as a workaround for this problem and looks like I don't have a choice

Thanks a lot.

[#6]

Can you expand on this? I'm not sure what you mean by 'creating a table in the script.'

I have the same issue as the poster where I want to sort an expression and only show the top 10.

[#7]

I had the same problem and that is what I found.

A Pivot table can be sort by an expression but only for the 1st expression. So if you need to sort by some other expression but the 1st I'd recommend a workaround which is create your 1st expression as your sort expression but set the width to be the smallest and the font color to be the same as the background, so the users will not see that. Then in the expression where you want to actually see that formula, simply say =column(1).

That works but I agree it is a workaround, not a solution that we can be proud of.

[#8]

I solved the issue by just changing from a pivot table to a straight table. I didn't really need to pivot anyway so this was a good solution.

[#9]

Sorry this is terrible... a major flaw, not to be able to sort any dimension of a pivot table by an expression...

Igor your work around is fantastic - thank you