Variables in set analysis

[Anonymous]

ok, how do I incorporate variables in this?  I want to incorporate my variables vStartDate and VEndDate

if(Scenario='Actual',(only({$<Month_Year={'>=1/1/13<=3/1/15'}>}[Price per Unit])),only({$}[Price per Unit]))

I've tried replacing the hard coded dates with my variables but that does not work.

[robert_mika · Accepted solution]

Have you used dollar expansion?

Like

{'$(vStartDate)'}

[maxgro]

Are you sure your expression works? I have some doubt about

- only

- Month_Year field with start/end date variables

if(Scenario='Actual',

     only({<Date={">=$(vStartDate)<=$(vEndDate)"}>}  [Price per Unit]), 

     [Price per Unit]

     )

[Anonymous]

Not sure why exactly but this works:

=if(Scenario='Actual',(only({$<Month_Year={'>=$(#=vStartDate)<=$(#=vActEndDate)'}>}[Price per Unit])),only({$}[Price per Unit]))

I saw #= used in another thread on this site for something similar.  What does the #= do?

[maxgro]

I think it doesn't matter (#) in your case.

This is from Qlik Reference Manual

Dollar-Sign Expansion Using a Variable

When using a variable for text replacement in the script or in an expression, the following syntax is used:

$( variablename )

$( variablename) expands to the value in variablename. If variablename does not exist the expansion will

be the empty string.

For numeric variable expansions, the syntax $( variablename ) will generate a number using the regional decimal

separator, i.e. for many countries a decimal comma. Such an expansion should not be used for numbers

inside the script since these must use decimal point. Instead the expansion $(# variablename ) should be

used. (Note the hash sign). It always yields a valid decimal-point representation of the numeric value of variablename,

possibly with exponential notation (for very large/small numbers). If variablename does not exist

or does not contain a numeric value, it will be expanded to 0 instead.

Example:

After execution of the following script:

SET DecimalSep=',';

LET X = 7/2;

$(X ) will expand to 3,5 while $(#X ) will expand to 3.5.

[Anonymous]

It does seem to matter.  It doesn't work without the #=.  It doesn't work without the = or without the #.  The formula needs both.  Anybody know what this combo means?

[maxgro]

could you post your .qvw?

[Kushal_Chawda]

try,

if(Scenario='Actual',(only({$<Month_Year={">=$(vStartDate)<=$(vEndDate)"}>}[Price per Unit])),only({$}[Price per Unit]))

where ,

vStartDate =date(date#('1/1/13','MM/DD/YYYY'),'MM/DD/YYYY')

vEndDate =date(date#('3/1/13','MM/DD/YYYY'),'MM/DD/YYYY')

[#9]

Hi,

I think u can use it,

if(Scenario='Actual',(only({$<Month_Year={">=$(vStartDate)<=$(vEndDate)"}>}[Price per Unit])),only({$}[Price per Unit]))

where ,

vStartDate =date(date#('1/1/13','MM/DD/YYYY'),'MM/DD/YYYY')

vEndDate =date(date#('3/1/13','MM/DD/YYYY'),'MM/DD/YYYY')

[Anonymous]

Its proprietary data but I will create one with fake data and post