topic Re: Less Than or equal in expression in QlikView

Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 08:13:26 GMT

Dear Friends,

I have created following variable in Qv document

let vTdate=num(Date(MakeDate(2019,10,31),'YYYY/MM/DD'));

and I have written following expression in my pivot table and it works perfectly

SUM({<FYYYYMM={$(=vTmonth)},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

but when I try to change the expression as shown below it does not work

SUM({<FYYYYMM={"<=$(vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

I want to change the equal to less than or equal. Pls help me to correct the expression

Re: Less Than or equal in expression

asinha1991 — Wed, 16 Oct 2019 08:23:57 GMT

maybe try this

SUM({<FYYYYMM={"<=$(=vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

Re: Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 08:49:11 GMT

Thanks for your reply,

But it does not work

Re: Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 09:21:11 GMT

Very Sorry

I have missed to mention My expression for vTmonth . It is as follows

LET vFmonth=Date($(vFdate),'YYYYMM')

Re: Less Than or equal in expression

asinha1991 — Wed, 16 Oct 2019 09:24:54 GMT

check if this works.

SUM({<FYYYYMM={"<='$(vTmonth)'"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

if not can you check at bottom pane of expression dialog and let me know what this expression is evaluated to?

Re: Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 09:46:44 GMT

Dear Asinha

It is still not working and expression evaluation is like below

Re: Less Than or equal in expression

asinha1991 — Wed, 16 Oct 2019 10:00:15 GMT

is FYYYYMM a date field? or a number field?

can you see what is evaluates to?

SUM({<FYYYYMM={"<=$(vTmonth)"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

also try

SUM({<FYYYYMM={"<=num#('$(vTmonth)')"},POL_TYPE={'R','A'},HISTORY={'Y'}>}PREMIUM)

Re: Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 10:14:36 GMT

Both Expression does not work

FYYYYMM is created in my master calendar with the syntax

text(DATE(POL_PERIOD_FROM,'YYYYMM'))AS FYYYYMM

Evaluation for 1st Expression

Evaluation for 2nd Expression

Re: Less Than or equal in expression

asinha1991 — Wed, 16 Oct 2019 10:18:48 GMT

this is the problem

text(DATE(POL_PERIOD_FROM,'YYYYMM'))AS FYYYYMM

creates text, text cannot be evaluated using > or <, hence it worked with only =

first expression should work in my opinion if you make FYYYYMM as num

something like this

num#(text(DATE(POL_PERIOD_FROM,'YYYYMM'))) AS FYYYYMM

Re: Less Than or equal in expression

upaliwije — Wed, 16 Oct 2019 10:54:15 GMT

Thanks

It is working. I was not aware that text fields does not work with <> signs. I learned it from you. Thanks you very much. The other this I am not very familiar with these date functions. Can u pls recommend some documents to read.