Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I am wondering what's the difference between typing in directly in the Expression (fx) box directly vs. creating a set operations.
I am baffled a bit because this expression below works perfectly when I write directly in the expression(fx) box:
sum({<date = {'>=$(=MonthStart(Max(date)))'}>*<date = {'<=$(=MonthEnd(Max(date)))'}>}amount)
But when I create this set expression, current month amount, I get an incorrect answer:
set current_month_amount= "sum({<date = {'>=$(=MonthStart(Max(date)))'}>*<date = {'<=$(=MonthEnd(Max(date)))'}>}amount";
Any help is appreciated.
If you are setting up/storing the expression in a variable, then you have to use $(vVariable) in your other expression wherever you are using this variable.hope that helps.
Hello, I do call $(current_month_amount), but the amount it gives is incorrect.
please post your expressions with some sample data.
The problem is that the $ expansions (DSE) are executing even though you may not expect it in a Set statement. You need to break up the DSEs to prevent them executing at this point. You can do this
Set current_month_amount = Sum({<date = {'>=#(=MonthStart(Max(date)))'}> * <date = {'<=#(=MonthEnd(Max(date)))'}>}amount;
Let current_month_amount = Replace(current_month_amount, '#', '$');
or this
Let current_month_amount = "sum({<date = {'>=$" & "(=MonthStart(Max(date)))'}> * <date = {'<=$" & "(=MonthEnd(Max(date)))'}>}amount";
This is only a problem if the expression is defined in the script on in an inline load. It is not a problem if it is read from an external source.