topic Re: Max client name based on expression in QlikView

Max client name based on expression

vvvvvvizard — Wed, 25 Jul 2018 08:43:20 GMT

LOAD * Inline [

Client , Tickets

A , 20

b , 20

C , 30

A , 20

D , 5

];

This return the highest number of tickets a client has

=Max(aggr(Sum(Tickets),Client))

Now how do I return the client name with the highest number of tickets in a textbox

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 08:48:44 GMT

Hi,

you can try this on a textbox:

=FirstSortedValue(Client,-Tickets)

Re: Max client name based on expression

vvvvvvizard — Wed, 25 Jul 2018 10:32:41 GMT

I edited the inline table it so both A add up to 40 , instead your expression returns C , instead of A

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 11:42:08 GMT

because the values are not grouped in the back end, you need to group your measure by the Client dimension using Aggr()

like this:

=FirstSortedValue(Client,-aggr(sum(Tickets),Client))

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 11:45:33 GMT

Or this

Only({<Client = {"=Rank(Sum(Tickets)) = 1"}>}Client)

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 11:49:36 GMT

Hi Sunny,

I was thinking about this and try to use it, but didn't work, I think it need some grouping inside the Rank function..

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 11:53:56 GMT

Grouping inside rank? Don't we already have Sum() to group?

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 11:59:19 GMT

Yes, because here the Rank 1 you will find on A and C, so the expression will take which Client Value ?

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 12:01:12 GMT

I tried this and it worked:

=Only({<Client = {"=Rank(aggr(Sum(Tickets),Client,Tickets)) = 1"}>}Client)

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 12:05:30 GMT

But this didn't work?

Only({<Client = {"=Rank(Sum(Tickets)) = 1"}>}Client)

That is so strange... let me give it a shot

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 12:07:04 GMT

Strange but what I provided seems to be working okay for me

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 12:07:26 GMT

didn't work for me, at least with op's sample data

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 12:09:52 GMT

Check the sample I have attached below... it did work for me based on OP's sample

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 12:15:06 GMT

Actually I don't know if OP re changed his inline or I'm just using his previous sample data, but I'm using this

LOAD * Inline [

Client , Tickets

A , 10

b , 20

C , 30

A , 20

D , 5

];

and with this data, the expression using rank don't work for me

Re: Max client name based on expression

sunny_talwar — Wed, 25 Jul 2018 12:17:17 GMT

He is using

LOAD * Inline [

Client , Tickets

A , 20

b , 20

C , 30

A , 20

D , 5

];

and the reason it doesn't work in your case is because there is a clash for Rank 1... try this with your data because of the clash

=Concat(DISTINCT {<Client = {"=Rank(Sum(Tickets)) = 1"}>}Client, ', ')

Re: Max client name based on expression

YoussefBelloum — Wed, 25 Jul 2018 12:21:19 GMT

Understood, was using his previous sample..

Thanks