modify set analysis to exclude certain values doesn't work

[chriscools]

Hello,

i have an expression:

num(sum ( {< OrderStatus={"On hold", "Placed" , "Closed"} ,  Year = {'$(= Year( Today()))'},Week =  {'$(=   Week(             Today())-1)'}        >}                      OrderQty*OrderPriceUnitNet),'#.###,##€')

but i want to change it to also exclude all orders which have "OnHoldReason= Quotation".

so i changed it into this:

num(sum ( {< OrderStatus={"On hold", "Placed" , "Closed"}, OnHoldReason- ={"Quotation"},     Year = {'$(= Year( Today()))'},Week =  {'$(=   Week(             Today())-1)'}        >}                      OrderQty*OrderPriceUnitNet),'#.###,##€')

but it doesn't seem to work, it gives me nothing as a result.

How should i exclude this?

thanx!

chris

[fabienmd]

Hi,

Try to remove the space between "-" and "=".

num(sum ( {< OrderStatus={"On hold", "Placed" , "Closed"}, OnHoldReason -={"Quotation"},     Year = {'$(= Year( Today()))'},Week =  {'$(=   Week(             Today())-1)'}        >}                      OrderQty*OrderPriceUnitNet),'#.###,##€')

[chriscools]

Hello,

sorry for the late reply, i was on a holiday.

that i also tried, but doesn't help...

as soon as i put the "-" before the equal sign it also doesn't show the fields after that in red anymore.

it seems as if the "-" is not recognized.

perhaps something else i can try?

grtz,

chris

[fabienmd]

Hi,

"-=" is not recognized by the QlikView syntax highlighting even if it works fine.

Did you check if that works without the space, or did you see that the color wasn't right and didn't check any further ?

[chriscools]

Hey,

no i checked it.

and before i checked the figures also so i am sure i really have to get something when doing this.

i add the file to this post.

grtz,

chris

[DavidFoster1]

Fabien MAURIS+DEMOURIOUX's solution works. Just put the '-' in front of the '=' and you get the result 9.159,00€.

The expression is:

num(sum ( {< OrderStatus={"On hold", "Placed" , "Closed"},OnHoldReason -={"Quotation"},Date =  {'$(=Date(Today()))'}>} @OrderQty*OrderPriceUnitNet),'#.###,##€

[Miguel_Angel_Baeyens]

Hi,

It's working for me. Find attached.

Miguel

[chriscools]

Hey all,

thanx for your answers!

however it does not count the amount in the status placed that you see in the tabel.

so the total should not say 9159€ but  10898€.

What needs to be done to include the status placed again?

grtz,

Chris

[Gysbert_Wassenaar · Accepted solution]

Try this:

=num(sum({<OrderStatus={"On hold", "Placed" , "Closed"},

                    OnHoldReason -={"Quotation"},

                    Date={'$(=Date( Today()))'} >

                   +

                   <OrderStatus={'Placed'},

                     Date={'$(=Date(Today()))'} >

                 } OrderQty*OrderPriceUnitNet),'#.###,##€')

[DavidFoster1]

Alternatively try to avoid NULL values in your dimensions. In your data model try converting NULL's to 'Not Applicable', 'NA', 'BLANK' or something like that it avoids issues with NULLs trapping your logic.