Get Dimension with Highest Value in straight table

[sameershivalkar]

Hello Team,

I am new to qlik,need your help.

I have created a straight table where i want to display total amount for customer ie if customer A has value 144,444,78 then in the output 666 need to display against A.

Values in dimension field would be value with highest amount ie in our case details Account 123 data need to be displayed .

Customer	Account	Date	Code	Amount
A	12	06-04-2017	MA	144
A	123	07-02-2019	TD	444
A	125	04-01-2016	SD	78
B	54	03-03-2019	SD	74
B	74	01-01-2016	MA	52

Output

Output
Customer	Account	Date	Code	Amount
A	123	07-02-2019	TD	666
B	54	03-03-2019	SD	126

I have already displayed total value sum(aggr(sum(Amount),customer) but i am not able to display fields against highest amount ie as Account 123 has highest Amount so that data need to be displayed

A

123

07-02-2019

TD

Thanks in advance

[JordyWegman]

Hi Sameer,

Try this: 

IF(Max(Aggr(Sum(Amount),Customer,Account)) = Max(Total Aggr(Sum(Amount),Customer,Account)),Sum(Amount), null())

Jordy

Climber 

[sameershivalkar]

Hello Jordy,

Need to display fields against highest value.

Regards,

Sameer

[sunny_talwar]

Try this may be

Sum(Aggr(If(Sum(Amount) = Max(TOTAL <Customer> Aggr(Sum(Amount), Customer, Account)), Sum(TOTAL <Customer> Amount)), Customer, Account))

[JordyWegman]

Hi Sameer,

Why do you want the customer and account number? Because 666 is not related to that account number, only to the customer because for the account its 444.

If you do want it then do this for your measure:

Sum(Aggr(Sum(Amount),Customer))

And this for the account as a calculated dimension:

IF(Max(Aggr(Sum(Amount),Account)) = Max(Total Aggr(Sum(Amount),Account)), Account, null())

Jordy

Climber

[sameershivalkar]

Thank you so much sunny this worked i was struggling for this from so long 🙂

[sameershivalkar]

Also need a small help if total Amount is greater than 400 then Row would be displayed