Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For the below mentioned data
Customer Sales
A 10
A 20
A 30
B 10
B 20
B 10
I want to know the customer with maximum sale. The answer i expect is A. I have got the sum using aggr() function of 60 but don't know how to get "A" as an output. Any help would be highly appreciated. Also i am new to Qlikview so pardon my ignorance. Moreover i also need to find the 2nd and 3rd best customer so if possible please suggest the changes required for that.
Thanks in advance.
This should work , it uses a 'search mask' in the set analysis modifier to filter for specific customers. Its designed to bring back 1 customer. I use only() to bring back a text based value. You could also use concat()
=only( distinct {<Customer={"=rank( aggr(sum(Sales),Customer))=1"}>} Customer)
and for #2....
=only( distinct {<Customer={"=rank( aggr(sum(Sales),Customer))=2"}>} Customer)
This should work , it uses a 'search mask' in the set analysis modifier to filter for specific customers. Its designed to bring back 1 customer. I use only() to bring back a text based value. You could also use concat()
=only( distinct {<Customer={"=rank( aggr(sum(Sales),Customer))=1"}>} Customer)
and for #2....
=only( distinct {<Customer={"=rank( aggr(sum(Sales),Customer))=2"}>} Customer)
Hi,
Try this expressions
For Rank 1:
=only( {<Customer={"=rank( aggr(sum(Sales),Customer))=1"}>} Customer)
For Rank 2:
=only( {<Customer={"=rank( aggr(sum(Sales),Customer))=2"}>} Customer)
Hope this helps you.
Regards,
Jagan.
Hi Abhishek,
Try this in Dimension:
=if(aggr(rank(sum(Sales)),Customer)=1,Customer) // if you want to display only the first
=if(aggr(rank(sum(Sales)),Customer)<=2,Customer) // if you want rank 1 and rank 2
In expression:
sum(Sales)
Note: make sure to check suppress when value is null.
Regards
KC
Hi
please use below script in edit script as well as please find attachment
TEMP:
LOAD * INLINE [
CUSTOMER, SALES
A, 10
A, 20
A, 30
B, 10
B, 20
B, 30
];
INLINE:
NoConcatenate
LOAD
CUSTOMER,
SUM(SALES ) AS SALES
Resident TEMP Group BY CUSTOMER ;
DROP TABLE TEMP;
Hi,
Thanks a lot for the reply. The solution proposed works for me perfectly. Though i was wondering what should be the approach in case two customer has same rank as only() function returns NULL in those cases.
Thanks,
Abhishek