topic Re: SUM IF MAX in QlikView

SUM IF MAX

Thu, 11 Sep 2014 14:47:58 GMT

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

Re: SUM IF MAX

MK_QSL — Thu, 11 Sep 2014 15:18:57 GMT

Can you please provide sample data?

Re: SUM IF MAX

gmoraleswit — Thu, 11 Sep 2014 16:14:14 GMT

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?

Re: SUM IF MAX

Thu, 11 Sep 2014 17:04:16 GMT

Hi,

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

Hope this helps

Re: SUM IF MAX

Thu, 11 Sep 2014 17:05:35 GMT

or try removing total also if that doesn't work

Re: SUM IF MAX

Thu, 11 Sep 2014 17:40:32 GMT

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

Re: Re: SUM IF MAX

Fri, 12 Sep 2014 07:25:55 GMT

Hi Gabriela,

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

But here comes a sample file!

Thanks in advance,

Olle

Re: Re: Re: SUM IF MAX

tresB — Fri, 12 Sep 2014 07:31:47 GMT

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

PFA

Re: Re: Re: SUM IF MAX

Fri, 12 Sep 2014 07:36:10 GMT

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

Re: SUM IF MAX

jagan — Fri, 12 Sep 2014 07:36:33 GMT

Hi,

Try this expression

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

Regards,

Jagan.

Re: Re: Re: SUM IF MAX

tresB — Fri, 12 Sep 2014 07:44:20 GMT

That should be little simpler

=FirstSortedValue(Amount, -SettlementOrder)

PFA

Re: SUM IF MAX

Fri, 12 Sep 2014 07:47:40 GMT

Perfect!

Thank you Jagan!

Re: SUM IF MAX

IAMDV — Fri, 12 Sep 2014 07:56:31 GMT

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,

www.QlikShare.com

Re: Re: SUM IF MAX

Fri, 12 Sep 2014 09:16:13 GMT

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

Re: Re: SUM IF MAX

jagan — Fri, 12 Sep 2014 09:20:11 GMT

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.

Re: Re: SUM IF MAX

Fri, 12 Sep 2014 09:40:10 GMT

Works like a charm! Thanks