topic Re: how to exclude a period and part of another field in set analysis? in QlikView

how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 09:19:09 GMT

Hello,

i have an expression to give me qty still to deliver from a certain ordertype code taken between 2 dates.

How do i need to change the formula, to give me everything that is still open, EXCEPT everything from "Voorverkoop" between the two dates?

this is the formula:

sum ({<Date ={">=1/11/2012<=30/11/2012"}, CustomerOrderTypeCode={"Voorverkoop"}>}OrderQtyOpen )

can someone give me an idea?

thanx!

chris

Re: how to exclude a period and part of another field in set analysis?

Mon, 03 Dec 2012 09:37:59 GMT

Hi,

Try this

sum({$-1<Date ={">=1/11/2012<=30/11/2012"}, CustomerOrderTypeCode={"Voorverkoop"}>}OrderQtyOpen )

Niranjan.

Re: how to exclude a period and part of another field in set analysis?

nirav_bhimani — Mon, 03 Dec 2012 09:44:15 GMT

HI,

The data you want to exclude use - sign before = sign of that field.

eg.

In your case I am excluded CustomerOrderTypeCode with - sign.

sum ({ <Date = P( { <Date= ">=1/11/2012<=30/11/2012"}>} ), CustomerOrderTypeCode -= {" Voorverkoop "}>} OrderQtyOpen)

Regards,

Nirav Bhimani

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 09:54:17 GMT

Hey,

will this help to exclude only the orderqtyopen from "Voorverkoop" between the two dates?

all the orderqtyopen with an customerordertypeCode different from Voorverkoop can be shown.

for example

date = 15/11/2012

customerOrderType= CashAndCarry

in this case orderqtyopen must be taken into account.

grtz,

chris

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 09:54:55 GMT

Hello,

thank you for your reply, can you tell me what the "P" just does in your expression?

thanx!

chris

Re: how to exclude a period and part of another field in set analysis?

nirav_bhimani — Mon, 03 Dec 2012 09:59:15 GMT

Hi,

P Representing the element set of possible values of a filed.

Have you got your ans? If set analysis ans is helpful then tick as helpful ans or correct ans.

Regards,

Nirav Bhimani

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 10:11:25 GMT

Hello,

thanx for the explanation!

i'm not sure that your proposal also includes this:

for example

date = 15/11/2012

customerOrderType= CashAndCarry

in this case orderqtyopen must be taken into account.

grtz,

chris

Re: how to exclude a period and part of another field in set analysis?

Mon, 03 Dec 2012 10:14:30 GMT

Hi,

you can use as well E instead of P for Excluded values

christian

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 10:42:50 GMT

Thanx,

and how would i then need to paste it into this, just to exclude the values from "Voorverkoop" between 1/11/2012 and 30/11/2012, and everything else must be shown?

thanx!

chris

sum ({<Date ={">=1/11/2012<=30/11/2012"}, CustomerOrderTypeCode={"Voorverkoop"}>}OrderQtyOpen )

Re: how to exclude a period and part of another field in set analysis?

nirav_bhimani — Mon, 03 Dec 2012 10:47:24 GMT

Hi,

Sorry I didnt get your question.

CustomerOrderType and CustomerOrderTypeCode both are diff ????

Now you want to consider customerOrderType="CashAndCarry" as a part of expression, is it so?

Regards,

Nirav Bhimani

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 11:08:17 GMT

Hello,

i'm sorry about being messy in my explanation... 🙂

there is no difference between the 2 fields, the fiels is only CustomerOrderTypeCode.

i do not especially want to include "cashAndCArry" as a part of my expression.

what i'm looking for is the expression to exclude only the values of OrderQtyOpen

where only the values with customerordertypecode= "Voorverkoop" and where the date falls between 1/11/2012 and 30/11/2012 are excluded.

all values with "Voorverkoop" but a date outside of 1/11/2012 and 30/11/2012 must still be included.

and all values where CustomerOrderTypeCode is different from "Voorverkoop" must be included (here the date is not important!

grtz,

chris

Re: how to exclude a period and part of another field in set analysis?

Mon, 03 Dec 2012 11:13:59 GMT

Hi,

sum({$-1<Date ={">=1/11/2012<=30/11/2012"}, CustomerOrderTypeCode={"Voorverkoop"}>}OrderQtyOpen )

try this, hope it will satiesfy all your requiremnt.

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 12:58:41 GMT

Hello,

when i search for a specific article i normally i get only 1 line:

when i include the "$-1" like in your proposal i get (without changing the selection ) 2 lines:

why could this be?

chris

Re: how to exclude a period and part of another field in set analysis?

chriscools — Mon, 03 Dec 2012 16:23:15 GMT

Hello,

i solved it like this:

sum ({< WarehouseName = {'WHS BE','Kontich'}, LocationFunction={'PICKING','BULK'}>} StockQty )

thank you all for your help and also Mr Wassenaar!

grtz,

chris