SUM IF MAX

[#1]

Hi,

trying to show the end balance. Using the below does not work,

SUM(IF(SettlementOrder=(MAX( SettlementOrder)),Balance))

If I add the "TOTAL" it works, but I need this figure for each date.

SUM(IF(SettlementOrder=(MAX(TOTAL SettlementOrder)),Balance))

TOTAL gives me one single value (based on largest historical SettlementOrder-number)

Any clues?

Kind REgards,

Olle

[tresesco]

That should be little simpler

=FirstSortedValue(Amount, -SettlementOrder)

PFA

[#3]

Perfect!

Thank you Jagan!

[IAMDV]

Hi,

I suggest using FirstSortedValue() with the Aggr() functions. Attached the sample QVW with the expression.

I hope this helps!

EDIT: Little late to the party!

Cheers,

DV

www.QlikShare.com

[#5]

Hi again Jagan,

If I wanted to hardcode for specific clients, where would i put the

{ <Client= {'ClientName'}>} into the code? Cannot get it to work properly.

=SUM(Aggr(IF(SettlementOrder= (MAX(TOTAL <Date> SettlementOrder)), Sum(Balance)), Date, SettlementOrder))

Thansk in advance,

Olle

[jagan]

Hi,

Try like this

=SUM({ <Client= {'ClientName'}>} Aggr(IF(SettlementOrder= (MAX({ <Client= {'ClientName'}>} TOTAL <Date> SettlementOrder)), Sum({ <Client= {'ClientName'}>} Balance)), Date, SettlementOrder))

Hope this helps you.

Regards,

Jagan.

[#7]

Works like a charm! Thanks