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
];
