Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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


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

MV

1 Solution

Accepted Solutions
neetha_p
Master
Master

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

View solution in original post

10 Replies
Not applicable
Author

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

neetha_p
Master
Master

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

paul_w_routledg
Creator II
Creator II

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

Dim1Dim2Expr
Ax1
Ay8
Az2
Bl15
Bm3
Bn2

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

Dim1Dim2Expr
Bl15
Ay8
Bm3
Az2
Bn2
Ax1
Not applicable
Author

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

MV

neetha_p
Master
Master

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

Not applicable
Author

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 ?

Not applicable
Author

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.

Not applicable
Author

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.

neetha_p
Master
Master

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