topic Re: set expression in QlikView

set expression

Thu, 12 Jun 2014 05:45:54 GMT

REC_DAT <= monthend & GR_DAT = “ NULL” or > monthend

i need the set expression for the above logic

REC_DAT and GR_DAT are the fields that contains date(DD-MM-YYYY)

Re: set expression

jagan — Thu, 12 Jun 2014 05:55:09 GMT

Hi,

Try like this

=Sum({<REC_DAT ={'<=$(=MonthEnd(Today()))'}, GR_DAT -={'*'} + {'>$(=MonthEnd(Today()))'}>} MeasureName)

Replace Today() with your required date, and MeasureName with your measure name.

Regards,

Jagan.

Re: set expression

Thu, 12 Jun 2014 05:58:28 GMT

Hi Bhanu , Please find the below expression.

{<REC_DAT ={"<=monthend"},GR_DAT={""}>}

Re: set expression

Thu, 12 Jun 2014 06:01:57 GMT

=Sum({<REC_DAT ={'<=$(=MonthEnd(fyear()))'}, GR_DAT ={'*'} + {'>$(=MonthEnd(fyear()))'}>} INV_QTY)

tried this but showing wrong values

=Sum({<REC_DAT ={'<=$(=MonthEnd(fyear()))'}, GR_DAT -={'*'} + {'>$(=MonthEnd(fyear()))'}>} INV_QTY)

and this is showing 0 for all the month fields

fyear is my fiscal year

Re: set expression

its_anandrjs — Thu, 12 Jun 2014 06:07:18 GMT

Try this way also

=Sum({<REC_DAT ={'<=$(=MonthEnd(YourDateField))'}, GR_DAT ={ '' } + {'>$(=MonthEnd(YourDateField))'}>}

=Sum({<REC_DAT ={'<=$(=MonthEnd(Today()))'}, GR_DAT ={ '' } + {'>$(=MonthEnd(Today()))'}>}

Note:- Compare the REC_DAT and GR_DAT with any Your Date field

Re: set expression

Thu, 12 Jun 2014 06:14:49 GMT

hi all

none are working actually and i am attaching the fields of both 'recdate' and 'gr date' and sum is to be done to INV_QTY field....could you please check whether the condtion works or not and if it works the ans is 0 or any number

Re: set expression

sasikanth — Thu, 12 Jun 2014 06:24:03 GMT

HI Bhanu

try some thing like this

sum(if(REC_DAT<MonthEnd(Today()) and GR_DAT='' or GR_DAT>MonthEnd(Today()) , INV_QTY))

Re: set expression

jagan — Thu, 12 Jun 2014 06:27:37 GMT

Hi,

What is the monthend value in your expression? Is it current MonthEnd? or any other logic?

Regards,

jagan.

Re: set expression

jagan — Thu, 12 Jun 2014 06:32:49 GMT

Hi,

You have data only till March-2014, then what is the monthend value?

Regards,

Jagan.

Re: set expression

jagan — Thu, 12 Jun 2014 06:36:20 GMT

Hi,

This expression is working but you do not have data for this expression

=Sum({<REC_DAT ={'<=$(=MonthEnd(Today()))'}, GR_DAT = (GR_DAT - {"*"}) + {'>$(=MonthEnd(Today()))'}>} INV_QTY )

Regards,

Jagan.

Re: set expression

its_anandrjs — Thu, 12 Jun 2014 06:36:54 GMT

Can you explain your expression with and example of the data.

Re: set expression

Thu, 12 Jun 2014 06:38:13 GMT

Hi,

no its for all the months in the fiscal year it starts from mar 2013 to current month

Re: set expression

its_anandrjs — Thu, 12 Jun 2014 06:39:04 GMT

You can try with

=Sum({<REC_DAT ={'<=$(=MonthEnd(Today()))'}, GR_DAT ={ '' } + {'>$(=MonthEnd(Today()))'}> INV_QTY}

Re: set expression

Thu, 12 Jun 2014 06:44:15 GMT

REC_DAT is where i get the stock to my place and i keep it in a warehouse and i calculate it for >monthend so i will check the total quantity that i got for the month and the GR_date is the thing that i have utilised the quantity so it should be greater than the date that i have received ...and if its null then the stock is still in the ware house so i have to calculate the total quantity that is present in the warehouse