Row details corresponding to max value

[#1]

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.

[JonnyPoole · Accepted solution]

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)

[jagan]

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.

[jyothish8807]

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

[#5]

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;

[#6]

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