topic Re: Fetching Value of Make having maximum Invoice Price for each Type in QlikView

Fetching Value of Make having maximum Invoice Price for each Type

mahesh_agrawal — Wed, 06 Jun 2018 11:55:06 GMT

Hi Folks,

I am trying to find out the value of "Make" field which has highest "Invoice" value for each "Type".

I have achieved it in the backend, but to make it more dynamic based on front end selection, I need to calculate it at UI level.

Example of Source Data: (NOTE: Full excel file for the same is attached)

Target Output for above partial data:

Thanks in advance for the help.

Cheers,

Mahesh

Chanty4u — Wed, 06 Jun 2018 12:00:55 GMT

try

FirstSortedValue( Type, -Invoce)

sunny_talwar — Wed, 06 Jun 2018 12:01:43 GMT

Try this

=FirstSortedValue(Make, -Invoice)

mahesh_agrawal — Wed, 06 Jun 2018 12:08:46 GMT

Thanks Sunny.

Apart from this, is there any way we can achieve the same using Nested aggregation with MAX() and AGGR().

You solution is quite simple and awesome. I am just trying to expand my knowledge on use of AGGR() function too.

Thanks again for the help and look forward to your reply.

sunny_talwar — Wed, 06 Jun 2018 12:14:12 GMT

Sure thing... try this

=Concat(Aggr(If(Max(TOTAL <Type> Invoice) = Invoice, Make), Type, Make, Invoice))

mahesh_agrawal — Wed, 06 Jun 2018 12:26:23 GMT

Thanks a lot GURU.