Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
tresesco
MVP
MVP

Dimension 1: State

Dimension 2: Company

Exp:
If(rank(Sum(Sales))=1, Sum(Sales))
Anonymous
Not applicable
Author

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 

tresesco
MVP
MVP

This?

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

Hi

PFA

 

Regards

Shivesh

Anonymous
Not applicable
Author

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

shiveshsingh
Master
Master

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
Master
Master

Capture.PNG

tresesco
MVP
MVP

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

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