Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Have you used dollar expansion?
Like
{'$(vStartDate)'}
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]
)
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?
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.
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?
could you post your .qvw?
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')
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')
Its proprietary data but I will create one with fake data and post