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

[MK_QSL]

Can you please provide sample data?

[gmoraleswit]

Maybe:

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

Can you provide more data, an example or screenshot? are you using this expression in a chart? what are your dimensions?

[#4]

Hi,

SUM(IF(SettlementOrder=firstsortedvalue(TOTAL SettlementOrder,-SettlementOrder)),Balance))

Hope this helps

[#5]

or try removing total also if that doesn't work

[#6]

Put the 'if' outside of 'Sum'. Like IF(SettlementOrder=MAX( SettlementOrder),Sum(Balance))

[#7]

Hi Gabriela,

not working Im afraid. And non of the below suggestions either.

But here comes a sample file!

Thanks in advance,

Olle

[tresesco]

=Sum(IF(SettlementOrder=($(=MAX( SettlementOrder))),Balance)) 

PFA

[#9]

Hi tresesco,

still just giving me the one balance (from the date with the largest amount of transactions). I need to show the end balance for each date.

Kind Regards,

Olle

[jagan · Accepted solution]

Hi,

Try this expression

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

Regards,

Jagan.