topic Summing Using "and" Conditional - Syntax Help in QlikView

Summing Using "and" Conditional - Syntax Help

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I'm trying to write a Sum expression where I sum based if A like B AND Y like Z.

Re: Summing Using "and" Conditional - Syntax Help

sunny_talwar — Wed, 14 Dec 2016 14:48:16 GMT

1st you don't have the set analysis correctly for CIO (incorrect placement of curly bracket)

=Num(Sum({$<[Phase] = {'Finance*'}, {CIO = {'*'}>} RangeMax([Estimated Saves Minus Implementation Cost], 0)/1000000),'$###0') + Num(Sum({$<[Phase] = {Booked}>} RangeMax([Finance Confirmed 2016 Savings], 0)/1000000),'$###0')

So you are looking to avoid selection in CIO regardless of selection in CIO? May be this:

=Num(Sum({$<[Phase] = {'Finance*'}, CIO>} RangeMax([Estimated Saves Minus Implementation Cost], 0)/1000000),'$###0') + Num(Sum({$<[Phase] = {Booked}, CIO>} RangeMax([Finance Confirmed 2016 Savings], 0)/1000000),'$###0')

Re: Summing Using "and" Conditional - Syntax Help

Wed, 14 Dec 2016 15:01:44 GMT

Bit syntax error in your orginal expression:

=Num(Sum({$<[Phase] = {'Finance*'}, CIO = {'*'}>} RangeMax([Estimated Saves Minus Implementation Cost], 0)/1000000),'$###0') + Num(Sum({$<[Phase] = {Booked}>} RangeMax([Finance Confirmed 2016 Savings], 0)/1000000),'$###0')

Re: Summing Using "and" Conditional - Syntax Help

pnn44794 — Fri, 16 Dec 2016 22:34:32 GMT

Hello Sunny:

My apologies for the delay in replying. Year End and it's crazy, Anyway, yes, this works and very simple. I continue to learn from one of the best! Thank you, as always.

Re: Summing Using "and" Conditional - Syntax Help

pnn44794 — Fri, 16 Dec 2016 22:35:54 GMT

Srikanth:

As with Sunny, my apologies for the delay in replying. This was helpful and thanks for catching the syntax error.