Rank Calculation

[Anonymous]

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.

[sunny_talwar]

May be this

Dimension

STATE

Expression

Rank(Sum({<Company = {'C1'}>} Sales))

[tresesco · Accepted solution]

Try like:

Aggr(if(Company='C1',Rank( Sum(Sales))), State,Company)

[Anonymous]

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.

[sunny_talwar]

Aggr() function is used to sort the data first by State and then by Company....

If statement is used to see only Company 'C1'

Rank function is calculating the rank of sum of sales with respect to State as the first dimension and Company as second dimension. So, in your actual scenario, make sure you list the Aggr() dimension in the correct order.

[Anonymous]

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.

[tresesco]

Could you share a sample app that shows the issue you are facing?

[Anonymous]

Thanks for the reply let me create a sample data for demonstration purpose.

[Anonymous]

Thanks for the solution, this is working fine for me, i found the mistake at my end.