topic Date not resolving in Variable in QlikView

Date not resolving in Variable

johngouws — Fri, 22 Apr 2016 11:43:31 GMT

Hi there,

I have expressions that contain set analysis. I am trying to get the expression to work in a variable but cannot get the date to resolve. If I copy the expression into the variable it work correctly, but, when I create the variable in the script, this is where it goes wrong. I would like the expression in the variable to be re-created at every reload.

The expression that works in the chart is:

sum({<Source_Sort={'2'}, Date={"<=$(=date(MonthStart(Today(),0)-1))"}, [chart id]={'1004','1021','1022'} >} [DUE AMOUNT]).

In the script I have tried the following,

SET v_Due 'sum({<Source_Sort={'2'}, Date={"<=$(=date(MonthStart(Today(),0)-1))"}, [chart id]={'1004','1021','1022'} >} [DUE AMOUNT])';

Looking at the variable in the front end the date is always "Date={'<='" instead of showing the date.

I have also tried the following in the script:

LET v_Month_Close = "date(MonthStart(Today())-1,'YYYY MM DD')";

then

SET v_Due

sum({<Source_Sort={'2'}, Date={'<=$(=Date($(v_Month_Close)))'},[chart id]={'1004','1021','1022'} >} [DUE AMOUNT]);

Still the date does not resolve.

Any advise in this regard will be appreciated.

Regards,

John

Re: Date not resolving in Variable

ThornOfCrowns — Fri, 22 Apr 2016 11:47:36 GMT

Could you try changing all the double quotes to single quotes?

Re: Date not resolving in Variable

MK_QSL — Fri, 22 Apr 2016 11:49:41 GMT

Try

LET v_Month_Close = date(MonthStart(Today())-1,'YYYY MM DD');

Re: Date not resolving in Variable

sunny_talwar — Fri, 22 Apr 2016 12:13:15 GMT

Would you be able to share a sample where you are trying to do this, if the suggestions above doesn't work for you

Re: Date not resolving in Variable

johngouws — Fri, 22 Apr 2016 12:22:45 GMT

Hi Manish

I have tried:

LET v_Month_Close = date(MonthStart(Today())-1,'YYYY MM DD');

with the expression:

SET v_Due =

sum({<Source_Sort={'2'}, Date={'<=$(=Date($(v_Month_Close)))'},[tx chart id]={'1004','1021','1022'} >} [DUE AMOUNT]);

Looking at the variable in the front end it still shows:

sum({<Source_Sort={'2'}, Date={'<='},[tx chart id]={'1004','1021','1022'} >} [DUE AMOUNT])

Thanks for trying.

Re: Date not resolving in Variable

johngouws — Fri, 22 Apr 2016 12:25:41 GMT

Hi Sunny.

It would be SO nice to share this monster, but it may take a while to break it down.

Thanks anyway.

Re: Date not resolving in Variable

sunny_talwar — Fri, 22 Apr 2016 12:27:28 GMT

What about this:

LET v_Month_Close = Date(MonthStart(Today())-1,'YYYY MM DD');

with the expression:

SET v_Due = Sum({<Source_Sort={'2'}, Date={"<=$(v_Month_Close)"}, [tx chart id]={'1004','1021','1022'} >} [DUE AMOUNT]);

Re: Date not resolving in Variable

rubenmarin — Fri, 22 Apr 2016 12:28:04 GMT

Simple quotes and $-expansión can lead to this issues when creatin variables in script, can you try creating the variable like this?:

LET v_Due = Replace(Replace('sum({<Source_Sort={|2|}, Date={"<=%(=date(MonthStart(Today(),0)-1))"}, [chart id]={|1004|,|1021|,|1022|} >} [DUE AMOUNT])', '|', Chr(39)), '%', '$');

Re: Date not resolving in Variable

johngouws — Fri, 22 Apr 2016 12:53:24 GMT

Ruben,

You are a genius! Thank you, it works perfectly.

This is going to resolve a major problem for me.

Regards,

John