Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mahesh_agrawal
Contributor

Fetching Value of Make having maximum Invoice Price for each Type

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)

   

TypeMakeInvoice
HybridHonda17911
HybridHonda18451
HybridToyota18926
WagonAudi37060
WagonAudi44446
WagonBMW30110
WagonChevrolet20394
WagonChrysler28725

Target Output for above partial data:

 

TypeMAX(Invoice)Max Price maker
Hybrid18926Toyota
Wagon44446Audi

Thanks in advance for the help.

Cheers,

Mahesh

1 Solution

Accepted Solutions

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

Try this

=FirstSortedValue(Make, -Invoice)


Capture.PNG

View solution in original post

5 Replies
Chanty4u
Esteemed Contributor III

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

try

FirstSortedValue(  Type, -Invoce)

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

Try this

=FirstSortedValue(Make, -Invoice)


Capture.PNG

View solution in original post

Highlighted
mahesh_agrawal
Contributor

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

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.

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

Sure thing... try this

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

mahesh_agrawal
Contributor

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

Thanks a lot GURU.