topic PivotTable ordered multiple sizes in QlikView

PivotTable ordered multiple sizes

Thu, 11 Aug 2011 14:40:15 GMT

hello, I would like to know how can I sort a pivot table
divided into 2 blow up product ID, Warehouse and order in descending order by sum (Sales
thanks

exsemple

Product ID Warehouse sum(Sales)

A 001 20

002 09

B 001 15

002 10

003 09

PivotTable ordered multiple sizes

Oleg_Troyansky — Thu, 11 Aug 2011 14:53:45 GMT

Sorting pivot tables is tricky... You can sort each one of the Dimensions by an expression (use tab "Sort"), but keep in mind, that each Dimnsion will be sorted individually, without any regard to other dimensions. For example, in your case, if Warehouse 3 has more Sales Associated with it, it will appear on top, even for those Products where it's portion is not the highest.

In order to enforse sorting by Product and then Warehouse, you can try using Advanced Aggregation AGGR(), listing all the dimensions - this way, you might be able to sort it properly...

cheers,

Oleg

PivotTable ordered multiple sizes

its_anandrjs — Thu, 11 Aug 2011 15:04:20 GMT

Hi,

You need to sort by column orders like go to properties -> Sort -> product ID ascending order then Warehouse ascending order and then click on expression and type =sum(Sales) put ascending order to be selected and see the resut do you have any sample if so please provide me.

Regards,

Anand

PivotTable ordered multiple sizes

swuehl — Thu, 11 Aug 2011 15:44:33 GMT

Hi Massimo,

as already stated, sorting a pivot is kind of tricky (and I just noticed results are even depending if you are using partial sum on first, second or both dimensions).

It may be useful if you could describe, what your expected result of sorting should look like, e.g. I assume that you don't want something like:

Product ID Warehouse sum(Sales)

A 001 20

B 001 15

B 002 10

A 002 09

B 003 09

(I think you can't do that with a pivot anyway, use a straight table instead).

If you just want to sort first dimension by desc sum, then secon dimension, I got that by using sort by y value on first dimension, adding partial sums. You may want to play around what is best for your needs.

Stefan

Re: PivotTable ordered multiple sizes

Fri, 16 Mar 2012 13:51:47 GMT

You can concatenate your dimensions in a column.

You put that column in first so you can sort with this one.

You hide this column with the macro :

sub Collapse

call HideColumn("CH06", 1)

end sub

private sub HideColumn(ch, n)

set ch = ActiveDocument.GetSheetObject(ch)

ch.SetPixWidth (n-1), 0

end sub

but you to stop the posssibility of reduction

Regards,

Cyril