Passing a variable as a parameter

[Anonymous]

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"

1

2

3

4

5

6

7

8

9

10

11

12

];

Quarters:

NoConcatenate

LOAD * Inline [

"Selected Quarter"

1

2

3

4

];