topic Re: Rank Calculation in App Development

Rank Calculation

Anonymous — Sat, 16 Nov 2024 07:11:24 GMT

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.

Re: Rank Calculation

sunny_talwar — Wed, 28 Nov 2018 12:08:54 GMT

~~May be this~~

~~Dimension~~

STATE

~~Expression~~

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

Re: Rank Calculation

tresB — Wed, 28 Nov 2018 10:16:06 GMT

Try like:

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

Re: Rank Calculation

Anonymous — Thu, 29 Nov 2018 03:57:29 GMT

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.

Re: Rank Calculation

sunny_talwar — Thu, 29 Nov 2018 12:06:09 GMT

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.

Re: Rank Calculation

Anonymous — Fri, 30 Nov 2018 04:36:16 GMT

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.

Re: Rank Calculation

tresB — Fri, 30 Nov 2018 04:51:47 GMT

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

Re: Rank Calculation

Anonymous — Fri, 30 Nov 2018 04:53:47 GMT

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

Re: Rank Calculation

Anonymous — Fri, 07 Dec 2018 04:46:36 GMT

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