Date not resolving in Variable

[johngouws]

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

[ThornOfCrowns]

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

[MK_QSL]

Try

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

[sunny_talwar]

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

[johngouws]

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.

[johngouws]

Hi Sunny.

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

Thanks anyway.

[sunny_talwar]

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]);

[rubenmarin · Accepted solution]

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)), '%', '$');

[johngouws]

Ruben,

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

This is going to resolve a major problem for me.

Regards,

John