topic Re: Display the member with maximum invoice in a text object in QlikView

Display the member with maximum invoice in a text object

techvarun — Wed, 25 Sep 2013 13:28:12 GMT

Hi,

I have a table with structure

Name Invoicenumber

aaa 123

bbbb 123

i need to display only the name which processed maximum number of invoices in a text object

Thanks in advance

Regards

Re: Display the member with maximum invoice in a text object

techvarun — Wed, 25 Sep 2013 13:33:37 GMT

= FirstSortedValue(Name , - max(Count(Invoicenumber) )

but it is saying error in expreession

Re: Display the member with maximum invoice in a text object

swuehl — Wed, 25 Sep 2013 13:41:55 GMT

Try

= FirstSortedValue( aggr(Name,Name) , aggr(-count(Invoicenumber),Name) )

Re: Display the member with maximum invoice in a text object

techvarun — Wed, 25 Sep 2013 13:47:47 GMT

Thanks Swuehl

It worked like charm

Regards

Re: Display the member with maximum invoice in a text object

techvarun — Wed, 25 Sep 2013 13:59:16 GMT

Hi One more Qn.

How to find minimum of average?

= FirstSortedValue( aggr(Name,Name) , aggr(-min(avg(Invoicenumber)),Name) )

Regards

Re: Display the member with maximum invoice in a text object

swuehl — Wed, 25 Sep 2013 14:01:45 GMT

What should the avg(Invoicenumber) return [given your above sample data]? 123?

Re: Display the member with maximum invoice in a text object

techvarun — Wed, 25 Sep 2013 14:12:40 GMT

Hi swuehl

Not for that data I am using some other and data and found the average of time they are utilizing for processing certain invoices .

and now I need to show the member who is having least average response time (i.e) High Productivity.

= FirstSortedValue( aggr(ModBy,ModBy) , aggr(min((-avg(Diff))),ModBy) )

Regards

Re: Display the member with maximum invoice in a text object

swuehl — Wed, 25 Sep 2013 14:20:47 GMT

Then try

= FirstSortedValue( aggr(ModBy,ModBy) , aggr( avg(Diff),ModBy) )

FirstSortedValue() will return the min value sorted by your sort weight avg(Diff), if you want to get the max, add the minus sign.

And if you can have multiple ModBy's with the same min sort weight, FirstSortedValue won't return anything, to get at least one, add a DISTINCT qualifier:

= FirstSortedValue(DISTINCT aggr(ModBy,ModBy), aggr( avg(Diff),ModBy) )