topic Re: Set expression in QlikView

Set expression

Wed, 11 Jun 2014 10:46:41 GMT

Fields:

REC_DAT Received Date

WERKS Plant

MATNR Material Number

INV_QTY Invoice Quantity – 3PL qty

PCN_DAT PCN date

GR_DAT GR Date

Logic

For 3PL stock as on 30.04.2014

REC_DAT <= monthend & GR_DAT = “ ” or > monthend this is the logic that i should calculate for all the monthend value to get the 3pl stock

({<REC_DAT= {"<=$(=MonthEnd(Today()))"}, [GR Date]= {">$(=MonthEnd(Today()))", ''}>}

I tried the above expression but it doesnt work

Re: Set expression

tresB — Wed, 11 Jun 2014 10:56:58 GMT

In such queries attach a sample qvw to get faster solution.

Re: Set expression

Gysbert_Wassenaar — Wed, 11 Jun 2014 11:01:17 GMT

Try something like sum({<REC_DAT= {"<=$(=MonthEnd(Today()))"}, [GR Date]= {">$(=MonthEnd(Today()))"'}>} INV_QTY).

Note, you will have to make sure the date formats of REC_DAT and GR Date match the default date format of your document.

Re: Set expression

Wed, 11 Jun 2014 11:11:23 GMT

thats not working and i have tried it before
and date formats is same for all

Re: Set expression

ali_hijazi — Wed, 11 Jun 2014 11:12:08 GMT

what is the format of REC_DAT and GR_DAT are they numeric or readable dates?

MonthEnd(Today()) will return a value that is formatted and not numeric

Re: Set expression

Wed, 11 Jun 2014 11:14:37 GMT

31-04-2014(DD-MM-YYYY) this is the format for all the date fields in the application

Re: Set expression

Gysbert_Wassenaar — Wed, 11 Jun 2014 11:15:33 GMT

Make sure your date fields actually contain data values and not string values that only look like dates.

Re: Set expression

ali_hijazi — Wed, 11 Jun 2014 11:16:30 GMT

if your date fields are numeric use $(=num(DayStart(MonthEnd(Today()))))

Re: Set expression

ali_hijazi — Wed, 11 Jun 2014 11:17:50 GMT

I would update these values by using num(DayStart(Rec_Dat)) as Rec_Dat in the script

then in the expression us $(=num(DayStart(MonthEnd(Today()))))

date values that are formatted are hideous

Re: Set expression

Wed, 11 Jun 2014 11:29:16 GMT

this is the sample application have a look at it once

Re: Set expression

tresB — Wed, 11 Jun 2014 11:35:11 GMT

=sum({<REC_DAT= {"<=$(=MonthEnd(Today()))"}, GR_DAT= {">$(=MonthEnd(Today()))"}>} INV_QTY)

Works. Note, in your sample qvw you would get zero as the GR_DAT condition fails. Try with full data.

Re: Set expression

ali_hijazi — Wed, 11 Jun 2014 11:49:53 GMT

if you change the date format on your PC it won't work

Re: Set expression

Wed, 11 Jun 2014 12:19:44 GMT

what if the condition for GR_DAT can be null and monthend can i have the expression for both the condition that can be null and >monthend

another query whether the above expression provides only for my current month or it provides for all the months