topic Row details corresponding to max value in QlikView

Row details corresponding to max value

Mon, 16 Mar 2015 10:59:09 GMT

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.

Re: Row details corresponding to max value

JonnyPoole — Tue, 17 Mar 2015 03:11:30 GMT

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)

Re: Row details corresponding to max value

jagan — Tue, 17 Mar 2015 04:34:34 GMT

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.

Re: Row details corresponding to max value

jyothish8807 — Tue, 17 Mar 2015 04:43:28 GMT

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

Re: Row details corresponding to max value

Tue, 17 Mar 2015 05:43:45 GMT

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;

Re: Row details corresponding to max value

Tue, 17 Mar 2015 20:24:19 GMT

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