0 Replies Latest reply: Feb 16, 2018 12:50 PM by Bob Libby RSS

    Passing a variable as a parameter

    Bob Libby

      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

      ];