topic Re: How do I sort a pivot table on an expression column ? in QlikView

How do I sort a pivot table on an expression column ?

Wed, 10 Dec 2014 14:00:27 GMT

Hi All,

I have a pivot table object on my dashboard with two dimension columns and an expression column. I would like to be able to Sort Descending on the values in the expression column. How do I do this ?

Many thanks

Re: How do I sort a pivot table on an expression column ?

Wed, 10 Dec 2014 14:07:00 GMT

Please try this: Sort -> Y-Value -> Descending

Re: How do I sort a pivot table on an expression column ?

Anonymous — Wed, 10 Dec 2014 14:07:26 GMT

Hi,

Object Properties - Sort tab - select the top dimension and enable the Y-value option for Sort by(descending). That will sort the pivot table using the values of the first expression of the chart.

Regards

Neetha

Re: How do I sort a pivot table on an expression column ?

Anonymous — Wed, 10 Dec 2014 14:22:25 GMT

You can't sort on an Expression in a pivot table.

When you think about it, it actually makes no sense.

If you sort on an Expression then what gets displayed as Dimensions? All the dimensions will be broken and values won't appear grouped together. Effectively you will no longer have a pivot table you will have a straight table.

Consider this pivot table with two dimensions and an expression

Dim1	Dim2	Expr
A	x	1
A	y	8
A	z	2
B	l	15
B	m	3
B	n	2

and now how it looks if sorted by the expression. The dimensions are all mixed up, drill down, subtotals etc cannot be supported.

Dim1	Dim2	Expr
B	l	15
A	y	8
B	m	3
A	z	2
B	n	2
A	x	1

Re: How do I sort a pivot table on an expression column ?

Wed, 10 Dec 2014 16:00:32 GMT

Thanks Neetha, that works great.

However, I want to give the user the option of sorting either on the expression, or on the first dimension (Account Name). How can I do this without instructing the user to go into Properties and change the Sort attributes. ?

Re: How do I sort a pivot table on an expression column ?

Anonymous — Wed, 10 Dec 2014 18:54:59 GMT

Hi Maureen,

Please check for below:

1. Goto Chart Properties -> Sort -> Allow Interactive Sort.

2. Next Presentation-> check 'Sort Indicator'

3.Check for User Preferences - Object tab - Show sort indicator check box is checked.

4. On Chart, Double Click on the Column Label.

Icon is display for sorted column. will sort as required

Regards

Neetha

Re: How do I sort a pivot table on an expression column ?

Thu, 11 Dec 2014 13:55:16 GMT

Hi again,

I'm afraid I don't have the option of 'Allow Interactive Sort' on the Sort tab of Properties.

Neither do I have a 'Sort Indicator' on the Presentation tab.

I'm using Qliview 11.

Is this a version issue ?

Re: How do I sort a pivot table on an expression column ?

Thu, 11 Dec 2014 13:59:38 GMT

Ah ! Your solution applies to a Straight Table only. Mine's a Pivot Table chart.

I'm going to have to think of a different way as I don't want to use a Straight Table.

Re: How do I sort a pivot table on an expression column ?

Thu, 11 Dec 2014 14:05:19 GMT

It does make sense if I want the data grouped and sorted thus:

Dim 1 Dim 2 Expr

A y 8

A z 2

A x 1

B l 15

B m 3

B n 2

and not as you suggested.

Re: How do I sort a pivot table on an expression column ?

Anonymous — Thu, 11 Dec 2014 14:07:26 GMT

Hi,

Mistake, it applies to only straight table.

Pivot table: Select the top dimension and enable the Y-value option for Sort by(Ascending/descending) in object properties. That will sort the pivot table using the values of the first expression of the chart.

No option for user to sort in UI for pivot table.

Regards

Neetha

Re: How do I sort a pivot table on an expression column ?

Thu, 11 Dec 2014 14:16:59 GMT

OK thanks. That's confirmed it then.