Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a question about sorting value by expression in pivot table.
i have a data like this
Col1 | Col2 | C ol3 | Col4 | Col5 | Total |
Loc | Area | 1 | 1 | A01 | 22 |
Loc | Area | 1 | 1 | A02 | 10 |
Loc | Area | 1 | 1 | A03 | 15 |
Loc | Area | 1 | 2 | A01 | 5 |
Loc | Area | 1 | 2 | A02 | 2 |
Loc | Area | 1 | 2 | A03 | 8 |
And I want to Sort Col5 according the total value (descending)
I've try to put expression in sort tab like this:
And the result of my table is like this:
Col4 has two value (1 and 2).
The first one sorted correctly (A01 --> 22,A03 -->15, and A02 --> 10)
But the second one is not correct, the result i expect is (A03-->8, A01 -->5, A02 -->2)
anybody has solution to solve this problem ?
I also attach the sample file.
Thanks in advance.
How you say second values are not correct, because your input showing same values. Have you checked the cross checking for UAT. Because, for me the values are correct
May be try with y-value sort
May be as below:
Take a look here: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level).
- Marcus
Hi, thanks for your idea ?
But can you explain about this expression ?
Sum(Aggr(Sum(Total), Col5, Col4))
How to decide i need to use Aggr function and put Col5 and Col4 as parameter ?
Thanks in advance.