topic Re: Pivot table sort by expression issue in QlikView

Pivot table sort by expression issue

Thu, 27 Feb 2014 08:40:14 GMT

Hi everyone.

I have a bit of a problem with sort by expression in pivot table. The table and data look something like this:

CURRENCY	ORGANISATION	EXPR1	THESUM=SUM(TR)
EUR	ORG1	-	80 000.00
	ORG2	-	2 000.00
	ORG3	-	10 000.00
RUR	ORG4	-	100 000.00
RUR	ORG2	-	60 000.00

I need to sort it by the values of THESUM (desc) for each CURRENCY + ORGANISATION row.

Dimensions are CURRENCY and ORGANISATION (+ a couple collapsed others which are irrelevant to this). Can't alter the table's structure in any way so placing THESUM as first expression and have a Y-sort is out of the question (even if it works - I checked ).

My current sort expression looks like this

CURRENCY by name

ORGANISATION by expression: sum(TOTAL <CURRENCY, ORGANISATION> TR)

So ORG2 has two currencies and it would seem that the sort just takes the sum of the entire ORG2 (which is 62k) instead of just a part that uses EUROS (2k).

This is how I want the table to look like:

CURRENCY	ORGANISATION	EXPR1	THESUM=SUM(TR)
EUR	ORG1	-	80 000.00
	ORG3	-	10 000.00
	ORG2	-	2 000.00
RUR	ORG4	-	100 000.00
RUR	ORG2	-	60 000.00

Is there something (probably trivial) I'm missing because this has been SO frustrating

Thanks in advance.

Re: Pivot table sort by expression issue

Thu, 27 Feb 2014 11:21:05 GMT

Hi,

This is probably a bit of an overly complicated way to go about it but you could try to add a calculated dimension using (Aggr(Sum(TR), ORGANISATION, CURRENCY)), sort by the currency column and then by the calculated dimension. Then you can hide the calculated dimension using the macro in the below link (i.e. the sub Squeeze one)

Hiding Dimension (pivot) by using Sheet Properties | Qlik Community

Re: Pivot table sort by expression issue

Thu, 27 Feb 2014 13:55:29 GMT

Nevermind. I found a way to trick the system. I am operating with 3 currencies so I just put 1, 2 and 3 spaces after ORGANISATION depending on the currency (that way the ORGANISATION dimension is technically combined with CURRENCY now and it makes the sort by expression work (still can't understand why it didn't before though) but is invisible).

I realise that it's a bit dumb and not very efficient but it's better than nothing