topic Re: Pivot table sorting with 2 calculated dimensions in QlikView

Pivot table sorting with 2 calculated dimensions

nivellen11 — Wed, 06 Jul 2016 11:52:30 GMT

Hi, I need to create table with two dimensions. First one with top 5 value based one first expression (expanded) + others row sowing sum of rest (collapsed) + Grand Total. 2nd dimension will show top 3 values based on 1st expression + total row. I manage to create top 5 and top 3 values but i have a problem with proper sorting. I've tried adding "hidden' expression with some calculation based on RANK and AGGR function but without success. Table should look like this:

Is this possible? if so then how?

Re: Pivot table sorting with 2 calculated dimensions

boorgura — Wed, 06 Jul 2016 14:48:58 GMT

do you just need it sorted descending??

If that's the case - simply sort by "Y-Value" descending

Re: Pivot table sorting with 2 calculated dimensions

marcus_sommer — Wed, 06 Jul 2016 14:54:53 GMT

This is the best source for this topic: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

- Marcus

Re: Pivot table sorting with 2 calculated dimensions

vinieme12 — Wed, 06 Jul 2016 15:11:57 GMT

For the expression you used add TOTAL keyword as below

Select your first dimension >> Sort by expression >> Descending

Uncheck Sort options for other dimensions.

SUM ( TOTAL<DIM1> yourexpression )