Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 find Rank 1 Company in State A,B,C in a pivot table.
The desired output is like.
STATE | Rank 1 Comapny | Sales of Rank1 Company |
A | C3 | i.e. sales of C3 = 1000 |
B | C3 | i.e. sales of C3 = 2000 |
C | C1 | i.e. sales of C1 = 4000 |
Please help, thanks in advance.
Dimension 1: State
Dimension 2: Company
Exp:
If(rank(Sum(Sales))=1, Sum(Sales))
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
This?
Aggr(If(rank(Sum(Sales))=1, Sum(Sales)),State,Company)
Hi
PFA
Regards
Shivesh
please can you provide me the logic as i dont have qlikview to open this file.
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
If you are interested in the name of the company try:
FirstSortedValue(Company, -Aggr(sum(Sales), State, Company))