topic Sort by expression in pivot table in QlikView

Sort by expression in pivot table

Thu, 17 Dec 2009 17:05:18 GMT

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

Sort by expression in pivot table

Thu, 17 Dec 2009 17:11:22 GMT

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

Use table-style instead.

Sort by expression in pivot table

Thu, 17 Dec 2009 17:45:49 GMT

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

Sort by expression in pivot table

Anonymous — Thu, 17 Dec 2009 18:01:33 GMT

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

Sort by expression in pivot table

Thu, 17 Dec 2009 18:25:58 GMT

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

Thanks a lot.

Sort by expression in pivot table

Wed, 30 Jun 2010 18:58:03 GMT

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.

Sort by expression in pivot table

Wed, 30 Jun 2010 19:11:13 GMT

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.

Sort by expression in pivot table

Wed, 30 Jun 2010 19:18:52 GMT

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.

Sort by expression in pivot table

Fri, 13 Aug 2010 00:59:18 GMT

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