QlikView documentation and resources.
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.
I will add some more explanations soon.
Known limitations:
Thanks for sharing Christof
nice!
If only there were an easier way via the interface though. Or something available for end-users via AJAX.
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.
Good work
Regards
ASHFAQ
Very nice and very clever!
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.
Very Good.
Do you have sample with Account (Finance)?
1...
11
111
1112
1113
2
......
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.
One small step for a man, one giant leap for qlik's Community
Regards,
Ely Malki
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
Good observation. Will mention it in my main blog. Thanks