topic Re: Can i add variables in expressions? if so how? in App Development

Can i add variables in expressions? if so how?

Anonymous — Wed, 02 Dec 2015 13:34:05 GMT

Hi Guys,

Can i add variables in expressions? if so how?

What am i doing wrong?

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

//vMonthPreviousX12

Re: Can i add variables in expressions? if so how?

marcus_sommer — Wed, 02 Dec 2015 13:43:17 GMT

The syntax isn't right - instead of:

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

it should look more so:

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

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

- Marcus

Re: Can i add variables in expressions? if so how?

undergrinder — Wed, 02 Dec 2015 13:53:47 GMT

Hi Lak,

I think the apostrophe is not valid in this expression.

Thread about variables in load script:

Field not found - <=>

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')

Re: Can i add variables in expressions? if so how?

Anonymous — Wed, 02 Dec 2015 13:54:14 GMT

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

Re: Can i add variables in expressions? if so how?

marcus_sommer — Wed, 02 Dec 2015 13:57:28 GMT

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

Re: Can i add variables in expressions? if so how?

Anonymous — Wed, 02 Dec 2015 14:01:42 GMT

So what's the best formats to use?

Re: Can i add variables in expressions? if so how?

JonnyPoole — Wed, 02 Dec 2015 14:04:06 GMT

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 ?

Re: Can i add variables in expressions? if so how?

reddy-s — Wed, 02 Dec 2015 14:12:29 GMT

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

Re: Can i add variables in expressions? if so how?

Anonymous — Wed, 02 Dec 2015 14:17:57 GMT

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

Re: Can i add variables in expressions? if so how?

JonnyPoole — Wed, 02 Dec 2015 14:29:03 GMT

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

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

Re: Can i add variables in expressions? if so how?

reddy-s — Wed, 02 Dec 2015 14:36:12 GMT

Hi Lak,

If you are using this in script try some thing like this:

let varPrev = year(today())&num(month(addmonths(today(),-1)));

load *;

SQL SELECT *

FROM db.table

WHERE field > '$(varPrev)';

Re: Can i add variables in expressions? if so how?

Anonymous — Wed, 02 Dec 2015 14:40:43 GMT

Thanks Guys, i'll try this later on or tomorrow and let you guys know how i get on.