Passing a variable as a parameter

Anonymous — Fri, 16 Feb 2018 17:50:53 GMT

Hi Experts,

I'm trying to pass a variable as a parameters to a formula. It works fine with a hard-coded value. Is it possible to do this and if so what syntax should I use?

In the attached qvw the Param_Sales column should change to FYR, YTD, QTD or MTD based on the selected TD_ID.

Ultimately I'd like to use $1 for the time dimension (FYR, YTD, QTD, MTD), $2 for the year and $3 for the measure ([Gross Sales] in this case.)

Any help is appreciated.

Thanks,

set CurrentYear = =Max([Selected Year]);

set LastYear = =Max([Selected Year])-1;

set CurrentMonth = =Max([Selected Month]);

set CurrentQuarter = =Max([Selected Quarter]);

set SelectTimeDimension = =Max([TD_ID]);

Let CY_Sales = '

if($1=1, sum( {$<[Calendar Year] = {$'&'(CurrentYear)}, [SD Billing Type] = {X}>} [Gross Sales] ),

if($1=2, sum( {$<[Calendar Year] = {$'&'(CurrentYear)}, [Calendar Month] = {"<=$'&'(CurrentMonth)"}, [SD Billing Type] = {X}>} [Gross Sales] ),

if($1=3, sum( {$<[Calendar Year] = {$'&'(CurrentYear)}, [Calendar Quarter] = {$'&'(CurrentQuarter)}, [SD Billing Type] = {X}>} [Gross Sales] ),

if($1=4, sum( {$<[Calendar Year] = {$'&'(CurrentYear)}, [Calendar Month] = {$'&'(CurrentMonth)}, [SD Billing Type] = {X}>} [Gross Sales] ),

))))';

Let CFYR_Sales = '$'&'(CY_Sales(1))';

Let CYTD_Sales = '$'&'(CY_Sales(2))';

Let Param_Sales = '$'&'(CY_Sales($(SelectTimeDimension)))';

Sales:

LOAD * INLINE [

Company Code, Calendar Year, Calendar Month, SD Billing Type, Gross Sales, Net Sales

C100, 2015, 3, X, 15100, 14101

C100, 2016, 5, X, 16100, 15101

C200, 2015, 1, X, 15200, 14201

C200, 2016, 6, X, 16200, 15201

C100, 2017, 3, X, 17100, 16101

C100, 2018, 5, X, 18100, 17101

C200, 2017, 1, X, 17200, 16201

C200, 2018, 6, X, 18200, 17201

];

TimeDimensions:

NoConcatenate

LOAD * Inline [

TD_ID, TD_Text

1, Full Year

2, Year to Date

3, Quarter to Date

4, Month to Date

];

Years:

NoConcatenate

LOAD * Inline [

"Selected Year"

2015

2016

2017

];

Months:

NoConcatenate

LOAD * Inline [

"Selected Month"

];

Quarters:

NoConcatenate

LOAD * Inline [

"Selected Quarter"

];

topic Passing a variable as a parameter in QlikView

Passing a variable as a parameter