topic Re: Variables in set analysis in QlikView

Variables in set analysis

Anonymous — Sat, 05 Sep 2015 19:11:51 GMT

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.

Re: Variables in set analysis

robert_mika — Sat, 05 Sep 2015 21:01:57 GMT

Have you used dollar expansion?

{'$(vStartDate)'}

Re: Variables in set analysis

maxgro — Sat, 05 Sep 2015 21:51:21 GMT

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]

)

Re: Variables in set analysis

Anonymous — Sun, 06 Sep 2015 00:08:08 GMT

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?

Re: Variables in set analysis

maxgro — Sun, 06 Sep 2015 07:03:53 GMT

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.

Re: Variables in set analysis

Anonymous — Sun, 06 Sep 2015 14:48:13 GMT

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?

Re: Variables in set analysis

maxgro — Sun, 06 Sep 2015 15:48:57 GMT

could you post your .qvw?

Re: Variables in set analysis

Kushal_Chawda — Sun, 06 Sep 2015 18:43:03 GMT

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

Re: Variables in set analysis

Mon, 07 Sep 2015 06:10:23 GMT

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

Re: Variables in set analysis

Anonymous — Tue, 08 Sep 2015 15:42:49 GMT

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