topic Re: Should I use an 'if/and' expression? in QlikView

Should I use an 'if/and' expression?

ljackson — Wed, 25 Sep 2013 13:19:30 GMT

Good afternoon everyone.

I am struggling to write an expression that has two conditions.

I have dimensions 'OrderSentqtyD' and 'ActWsvD'.

I need to sum the OrderSendqtyD, but excluding records where the OrderSendqtyD is -1 or less AND the ActWsvD equals zero.

So far I have been using the following expression successfully to exclude records with OrderSentqtyD -1 or less, but am unsure how to add the new condition of a zero ActWsvD:

Sum({$<OrderSentqtyD*={">-1"}>}OrderSentqtyD)

I have tried the following, but the results all come out with 0.

Sum({$<OrderSentqtyD*={">-1"},ActWsvD={"0"}>}OrderSentqtyD)

Do I need an 'if/and' expression? If so, how should it be set out?

Thank you all in advance, your help is greatly appreciated.

Leah

Re: Should I use an 'if/and' expression?

Clever_Anjos — Wed, 25 Sep 2013 13:24:23 GMT

Do you want include or exclude AcdWsvD = 0?

Sum({$<OrderSentqtyD*={">-1"},ActWsvD={"0"}>}OrderSentqtyD) //include

Sum({$<OrderSentqtyD*={">-1"},ActWsvD-={"0"}>}OrderSentqtyD) //exclude

Re: Should I use an 'if/and' expression?

ljackson — Wed, 25 Sep 2013 13:41:56 GMT

Just to clarify, If the record has both a negative OrderSentqtyD AND a zero ActWsvD, I need the record excluded.

(if it has negative OrderSentqtyD and ActWsvD above zero it has to be included, and vice versa if the OrderSentqty is positive and ActWsvD is zero it needs to be included. I only want the exclusion when both conditions apply to a record).

Hope this helps.

Re: Should I use an 'if/and' expression?

Clever_Anjos — Wed, 25 Sep 2013 13:47:55 GMT

Sum({$<OrderSentqtyD*={">-1"},ActWsvD-={"0"}>}OrderSentqtyD) should return what you want

Re: Should I use an 'if/and' expression?

ljackson — Wed, 25 Sep 2013 14:05:33 GMT

Perfect, thank you!

Re: Should I use an 'if/and' expression?

Clever_Anjos — Wed, 25 Sep 2013 14:13:47 GMT

you´re welcome