topic Sort Pivot Table on Expression in QlikView

Sort Pivot Table on Expression

jzimolong — Fri, 16 Jan 2015 17:57:01 GMT

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.

Re: Sort Pivot Table on Expression

jonasheisterkam — Fri, 16 Jan 2015 18:07:49 GMT

Is this what you want to create?

Re: Sort Pivot Table on Expression

Gysbert_Wassenaar — Fri, 16 Jan 2015 18:08:41 GMT

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

Re: Sort Pivot Table on Expression

jzimolong — Fri, 16 Jan 2015 18:14:06 GMT

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!

Re: Sort Pivot Table on Expression

jzimolong — Fri, 16 Jan 2015 18:16:54 GMT

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

Re: Sort Pivot Table on Expression

Gysbert_Wassenaar — Fri, 16 Jan 2015 18:18:28 GMT

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

Re: Sort Pivot Table on Expression

jonasheisterkam — Fri, 16 Jan 2015 18:23:05 GMT

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.

Re: Sort Pivot Table on Expression

Gysbert_Wassenaar — Fri, 16 Jan 2015 18:23:08 GMT

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