Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table sort by expression issue

Hi everyone.

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

CURRENCYORGANISATIONEXPR1THESUM=SUM(TR)

EUR

ORG1-80 000.00
ORG2-2 000.00
ORG3-10 000.00

RUR

ORG4-100 000.00
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:

CURRENCYORGANISATIONEXPR1THESUM=SUM(TR)

EUR

ORG1-80 000.00
ORG3-10 000.00
ORG2-2 000.00

RUR

ORG4-100 000.00
ORG2-60 000.00

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

Thanks in advance.

2 Replies
Not applicable
Author

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

Not applicable
Author

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