Using a variable in a Set modifier

[chloeM]

Hello,

I am trying to use a variable in a set modifier but have trouble doing it.

BAsically I would like to have something like this :

sum( {<  [demand week]={" '<='&$(vActiveWeek)"} >} Forecast_M1)

with vActiveWeek = 202501 for instance (I checked the value of vActiveWeek)

I have tried as well :

sum( {<  [demand week]={" <=$(vActiveWeek)"} >} Forecast_M1)

but it does not work.

and when I try :

sum( {<  [demand week]={" <=202501"} >} Forecast_M1)

it work perfectly well.

Any idea of what I am doing wrong to use the varaible in the set modifier ?

Thank you

Chloé

[Bhushan_Mahajan]

@chloeM Your set expression is right check the expression which is giving you value of vActiveWeek ?

Since same set expression is working for me.

[chloeM]

Thanks for your qlik reply, here is the expression of the variable :

Year(Today()) & num(Week(Today()),'00')

[Bhushan_Mahajan]

@chloeM In variable definition use =Year(max(CREATEDON)) & num(Week(max(CREATEDON)),'00') 

then set expression as =sum( {< [demand week] ={"<=$(vWeek)"} >} Forecast_M1)

[marcus_sommer]

Your variable contained a string and not a numeric value. You may change the value to:

year(today()) * 100 + week(today())

[QFabian · Accepted solution]

Hi @chloeM , did you create the variable in script ? if not, try this :

please see that they are right aligned, meaning that they are recognized as numbers

[chloeM]

Indeed, it worked when I put my variable in the script.

I have no idea why it did not work when the variable was set otherwise, I have tested all the solutions provided before, my variable was numeric (I could do operations on it) and I had tested in a table that [demand week]<=$(vActiveWeek) worked...

I will stick to the variable in the script but if someone have a possible explanation I am interested...

Thank you very much.

[marcus_sommer]

It's not quite the same if a value is used within a math operation or as part of a numeric comparison.

Many tools apply a conversion by measurements like this:

'2' * 1

which will then often result in 2 (of course depending on the tool and the used context). It's an implicit version of functions like num#(). This won't happens (at least I don't know that any tool does it) by:

'2' >= 1

Therefore my above suggestion to create (always) numeric values which is often not more difficult as concatenating numbers. Not only in variables else also for all fields which are used for any kind of calculating/matching - and ideally pure numbers without any formatting. It avoids a lot of trouble ...

[QFabian]

hope this help!

did you find    the    difference?