topic Re: Qlik Expressions (Direct vs. Set Operations) in QlikView

Qlik Expressions (Direct vs. Set Operations)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Qlik Expressions (Direct vs. Set Operations)

Qrishna — Wed, 28 Feb 2018 01:56:05 GMT

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.

Re: Qlik Expressions (Direct vs. Set Operations)

Anonymous — Wed, 28 Feb 2018 03:08:55 GMT

Hello, I do call $(current_month_amount), but the amount it gives is incorrect.

Re: Qlik Expressions (Direct vs. Set Operations)

Qrishna — Wed, 28 Feb 2018 03:19:31 GMT

please post your expressions with some sample data.

Re: Qlik Expressions (Direct vs. Set Operations)

jonathandienst — Wed, 28 Feb 2018 04:53:55 GMT

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.