PivotTable ordered multiple sizes

[#1]

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

[Oleg_Troyansky]

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

[its_anandrjs]

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

[swuehl]

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

D

[#5]

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