Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data set like the below data set which has State wise Company Sales.
State | Company | Sales | |
A | C1 | 500 | |
B | C1 | 600 | |
C | C1 | 4000 | |
A | C2 | 450 | |
B | C2 | 800 | |
C | C2 | 950 | |
A | C3 | 1000 | |
B | C3 | 2000 | |
C | C4 | 3500 |
The requirement is to calculate the rank of Company C1 in State A,B,C in a pivot table.
The desired output is like.
STATE | RANK OF COMPANY C1 |
A | 2 |
B | 3 |
C | 1 |
I am trying yo write the expression for "RANK OF COMPANY C1" in the pivot table but it didnt works.
Please help, thanks in advance.
May be this
Dimension
STATE
Expression
Rank(Sum({<Company = {'C1'}>} Sales))
Try like:
Aggr(if(Company='C1',Rank( Sum(Sales))), State,Company)
Can you please elaborate how exactly the expression is evaluated. It produces the correct result in the sample data set but the same logic is not working properly in actual application.
Hi, thanks for the reply, i have applied the same logic however the expression in providing wrong values of rank at many places. Still tying to figure it out.
Thanks for the solution, this is working fine for me, i found the mistake at my end.