topic Re: modify set analysis to exclude certain values doesn't work in QlikView

modify set analysis to exclude certain values doesn't work

chriscools — Fri, 30 Aug 2013 14:24:23 GMT

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

Re: modify set analysis to exclude certain values doesn't work

fabienmd — Fri, 30 Aug 2013 14:34:35 GMT

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),'#.###,##€')

Re: modify set analysis to exclude certain values doesn't work

chriscools — Wed, 04 Sep 2013 14:14:06 GMT

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

Re: modify set analysis to exclude certain values doesn't work

fabienmd — Wed, 04 Sep 2013 14:35:33 GMT

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 ?

Re: Re: modify set analysis to exclude certain values doesn't work

chriscools — Wed, 04 Sep 2013 15:29:56 GMT

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

Re: modify set analysis to exclude certain values doesn't work

DavidFoster1 — Wed, 04 Sep 2013 15:37:04 GMT

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),'#.###,##€

Re: Re: Re: modify set analysis to exclude certain values doesn't work

Miguel_Angel_Baeyens — Wed, 04 Sep 2013 15:39:53 GMT

Hi,

It's working for me. Find attached.

Miguel

Re: Re: Re: modify set analysis to exclude certain values doesn't work

chriscools — Wed, 04 Sep 2013 18:46:33 GMT

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

Re: modify set analysis to exclude certain values doesn't work

Gysbert_Wassenaar — Wed, 04 Sep 2013 19:00:32 GMT

Try this:

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

OnHoldReason -={"Quotation"},

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

<OrderStatus={'Placed'},

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

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

Re: modify set analysis to exclude certain values doesn't work

DavidFoster1 — Thu, 05 Sep 2013 07:48:32 GMT

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.