Sort Pivot Table on Expression

[jzimolong]

Through working on a different thread, came up with attached, which contains a Pivot Table that has 2 dimensions and 2 expressions.

The expressions use Rank to either display a count (and rank), or zero (which then is used to suppress rows).

How can this be sorted by the "RCA_WISE" column, in descending order?  I've tried so many different methods and nothing seems to work.  Is this even possible when using the Rank function?

It seems logical that an expression sort on the 2nd dimension would work, but I can't figure it out.

[jonasheisterkam]

Is this what you want to create?

[Gysbert_Wassenaar · Accepted solution]

Try first setting the sort order of DISP_CATEGORY to text Z->A and then enable sort by Y-Value Descending.

[jzimolong]

Nice, that looks perfect!  Wasn't even aware of the dual function.  I'll have to take a closer look and try to understand it more.  Appreciate the quick response!

[jzimolong]

That works as well, and no changes necessary to the dimensions, etc.  Never would have come across that solution.  Many thanks!

[Gysbert_Wassenaar]

Nice trick. It had me scratching my head for some minutes

[jonasheisterkam]

The second Dim is now a kombination of Dim1 and Dim2. So it can be sortet individual in ech Dim1 block. Dual is a number with text representation, different Number can have the same Text. So the number value is fieldindex(Dim1)*[Number to shift it to the left]+ fieldindex(Dim2)  =>num(fieldindex(Dim1)[000..]fieldindex(Dim2))

The Text repesentation is the text of Dim2.

[Gysbert_Wassenaar]

Well, I don't know why it works like that. I suspect it's a bug and that means it could work differently in future versions of Qlikview. But as long as you don't mind a bit of Q-fu-do....