Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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