Qlik Community

Qlik Sense App Development

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

hariprasadiffco
New Contributor

Sales of Top Rank Company

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 find Rank 1 Company in State A,B,C in a pivot table.

The desired output is like.

STATERank 1 ComapnySales of Rank1 Company
AC3i.e. sales of C3 = 1000
BC3i.e. sales of C3 = 2000
CC1i.e. sales of C1 = 4000

 

Please help, thanks in advance.

8 Replies
MVP
MVP

Re: Sales of Top Rank Company

Dimension 1: State

Dimension 2: Company

Exp:
If(rank(Sum(Sales))=1, Sum(Sales))
hariprasadiffco
New Contributor

Re: Sales of Top Rank Company

In my case i have only State as dimension in pivot table so this expression will only return Sales for Rank 1 State. so this is not fulfilling the requirement 

MVP
MVP

Re: Sales of Top Rank Company

This?

Aggr(If(rank(Sum(Sales))=1, Sum(Sales)),State,Company)
shiveshsingh
Honored Contributor

Re: Sales of Top Rank Company

Hi

PFA

 

Regards

Shivesh

hariprasadiffco
New Contributor

Re: Sales of Top Rank Company

please can you provide me the logic as i dont have qlikview to open this file.

shiveshsingh
Honored Contributor

Re: Sales of Top Rank Company

Hi

 

PFB

 

Script:

T:
LOAD * INLINE [

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
];

 

 

Dimension : -State, Company

Expression : if(rank(Sales)=1,Company)

 

In presentation Tab, please hide Dimension Company

 

 

 

 

shiveshsingh
Honored Contributor

Re: Sales of Top Rank Company

Capture.PNG

MVP
MVP

Re: Sales of Top Rank Company

If you are interested in the name of the company try:

FirstSortedValue(Company, -Aggr(sum(Sales), State, Company))
Community Browser