Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)), '%', '$');
Could you try changing all the double quotes to single quotes?
Try
LET v_Month_Close = date(MonthStart(Today())-1,'YYYY MM DD');
Would you be able to share a sample where you are trying to do this, if the suggestions above doesn't work for you
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.
Hi Sunny.
It would be SO nice to share this monster, but it may take a while to break it down.
Thanks anyway.
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]);
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)), '%', '$');
Ruben,
You are a genius! Thank you, it works perfectly.
This is going to resolve a major problem for me.
Regards,
John