QlikView Documents

[csw]

I hear a lot about Pivot Tables not sorting as wanted. That is true when you try to use individual formulas for each dimension. The formula is executed outside the dimensionality so that you get the same sort on all levels when you wanted the sort to be dependent on the given dimensionality.

So you think you have only one Plan B, to set the entire chart to sort "By y-Value". And then you have somewhere Year/Month in your dimensions and those, of course, should NOT sort by y-Value but by Year/Month ....

So in short, you would like to define a "A-Z" order on some dimensions and "y-Value" order on others. And then offer the user toggle buttons to change this on the fly.And y-Value may toggle between different columns (like Sales, #Orders ...)

Technically, you must set the Pivot to sort by "y-Value". That option is set on the 1st dimension and counts for all. Then you create an invisible first expression, which starts with

Pick(Dimensionality(), <sort formula for dim 1>, <sort formula for dim 2>, <sort formula for dim 3>)

If you want sort-on-the fly, you will have to offer the user action buttons to tweak the above sort formulas.

I have done it: Check this out. Download the QVW and click on the [Create $Sort] button.

• You don't need to repeat those steps yourself, a macro will do necessary steps:
  
• The macro is needed only during development to
  • add n text-boxes (where n is the number of dimensions
  • create n variables to save the current user's sort option
  • create the Pick(Dimensionality()...) formula for you, put it as 1st expression
  • set the new sort-column width to 0 px (to make it invisible

• The sort formula is "clever" enough to pick the dimensions using GetObjectField() formula, so if someone changes the order of the dimension columns it still works fine.
• In order to sort Z-A (alphabetically descending) we will need an additional field with a .SortIndex for each dimension value (doesn't matter if that .SortIndex is joined to the source tables or linked in separate tables. A generic code fragment is contained in the script to generate such .SortIndex fields if the data model is otherwise complete)
  

---

I will add some more explanations soon.

Known limitations:

• The displayed order does not match the internal row order, so functions like Above() and Below() refer to other rows than the obvious above and below row of the visible object
• The approach does not consider column dimensions (dimensions pivoted to horizontal), the formulas would need to be adjusted and I haven't looked into this.
• Although pivoting by the user (live change of the order of columns) would not break the functionality (as long as a dimension is not flipped into horizontal), I'd recommend to turn this off in the object
• The sorting will not work if you hide the Dimension Subtotals. Leave subtotals visible!

[amz]

Thanks for sharing Christof

[bill_lay_vc]

nice!

If only there were an easier way via the interface though. Or something available for end-users via AJAX.

[csw]

This approach is fully supported in Ajax, the macro is only a help for the developer in order not to create variables, action buttons and a hidden sort column manually.

[ashfaq_haseeb]

Good work

Regards

ASHFAQ

[rwunderlich]

Very nice and very clever!

[#7]

This is great.  I have a few cases where I can use this.  I am also working on a conditional sort for a Drill Down group right now.  Where I want the sort by expression desc at the property level then when drilled down to month then day, I want it to sort descending by date.  I haven't started too much work on it but I believe it might be simpler than the above.  If you know differently, let me know.  I start work on that next week.  I did tweak the sort orders but it keeps reverting back to Desc by Expression when it drills to month and day level.  In the group editor, each is selected to sort as I want, it just keeps reverting to sort by Expression regardless. 

[bestofwest]

Very Good.

Do you have sample with Account (Finance)?

1...

11

111

1112

1113

2

......

[ely_malki]

Hi Christof,

interesting solution, I would like to share alternative measure to obtain Z-A order, without back-end calculate index field, following your template structure.

1. aggr(NODISTINCT rank([<$GETDIM>],1,1),[<$GETDIM>])

One small step for a man, one giant leap for qlik's Community

Regards,

Ely Malki

[schlettig]

Hi Christof,

great generic solution for sorting in pivot tables.

I adapted it in one app  (w/ 18 Dims or so) and it's only sorting, when partial sums from the 2nd dim on are activated. In the moment i deactivate partial sums for one of the dimensions (other than the 1st) it sorts randomly (or natural).

Took me a while to figure it out, because i didn't activate partial sums at first.

Best regards,

Christian

[csw]

Good observation. Will mention it in my main blog. Thanks