Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

    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

    Screenshot 2014-10-13 17.38.01.png

    • 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!