Can i add variables in expressions? if so how?

[Anonymous]

Hi Guys,

Can i add variables in expressions? if so how?

What am i doing wrong?

Sum({<PRODUCT={"xxxxxxx"},SID_MONTH={$('vMonthPreviousX12')}>}Value)

//vMonthPreviousX12

[marcus_sommer]

The syntax isn't right - instead of:

... SID_MONTH={$('vMonthPreviousX12')} ...

it should look more so:

... SID_MONTH={'$(vMonthPreviousX12)'} ... // or

... SID_MONTH={"$(vMonthPreviousX12)"} ...

- Marcus

[undergrinder]

Hi Lak,

I think the apostrophe is not valid in this expression.

Thread about variables in load script:

Field not found - &lt;=&gt;

You can use variables In chart expression as well.

You should check it at an easier expression, like:

=if($(vFoo)=1,'Some text','Other text')

G.

[Anonymous]

Tried all those option still nothing, maybe the format of the variable has to be the same as the date field.

[marcus_sommer]

Yes this is right, not only the values must be matching also the formattings must be the same. This meant by month that Nov wouldn't match with 11.

- Marcus

[Anonymous]

So what's the best formats to use?

[JonnyPoole]

Its also important (as of 2.1) to assess the syntax of  the expression behind your variable and whether that syntax is valid in the context of where you are trying to apply it.

Related to that is whether the expression behind your variable has a leading '=' sign, which would tell Qlik to evaluate the expression before injecting the result into the expression, or evaluating within the context of the expression. Its different

Can you post the screenshot of the variable defiition for  vMonthPreviousX12 ?

[reddy-s]

Hi Lak,

check this expression for a sample:

count( {$ < [Days In Stock]={">$(varHigh)"} >}[Vehicle ID])

This document will give you all the insight you need about variables and its usage:

The Magic of Variables

[Anonymous]

Here you go

LET vMonthPreviousX12 = month(MonthStart(today())-12);

i also tried this

LET vMonthPreviousX12 = month(MonthStart(today())-12, YYYYMM);

the where clause Date field is called SID_MONTH and is formatted like this from the SQL warehouse 201509

[JonnyPoole]

As Marcus Sommer was suggesting, to create data equivalency on both sides you may need to adjust SID_MONTH in the  load editor to calculate as a month() as well . Something like this :

Load

     SID_MONTH

->

Load

     Month(Date#(SID_MONTH,'YYYYMM')) as SID_MONTH

After reloading you will know for sure that SID_MONTH is producing month() values in the same format as your variable.

Also i think you need to change you variable. I suggest the addmonths() function.

from

LET vMonthPreviousX12 = month(MonthStart(today())-12);

to

LET vMonthPreviousX12 = month(addmonths(today(),-11));