Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
hariprasadiffco
New Contributor

Rank Calculation

I have a data set like the below data set which has State wise Company Sales. 

 

StateCompanySales 
AC1500 
BC1600 
CC14000 
AC2450 
BC2800 
CC2950 
AC31000 
BC32000 
CC43500 

 

The requirement is to calculate the rank of Company C1 in State A,B,C in a pivot table.

The desired output is like.

STATERANK OF COMPANY C1
A2
B3
C1

 

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.

Labels (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rank Calculation

Try like:

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

Capture.JPG

8 Replies

Re: Rank Calculation

May be this

Dimension

STATE

Expression

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

 

MVP
MVP

Re: Rank Calculation

Try like:

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

Capture.JPG

hariprasadiffco
New Contributor

Re: Rank Calculation

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

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.
hariprasadiffco
New Contributor

Re: Rank Calculation

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.

MVP
MVP

Re: Rank Calculation

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

Re: Rank Calculation

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

Re: Rank Calculation

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

Community Browser