5 Replies Latest reply: May 22, 2018 2:43 PM by Lee Mychajluk RSS

    How to Reference a Variable in a Set Expression

    Lee Mychajluk

      Hi, all. Somwhat new to QS, and am trying to define a Master Item Measure that I can then use in other formulas to do Year over Year comparisons. The formula itself is just a FY sum of Revenues for the given Fiscal Year, which I want regardless of any other filters / selections. I think my primary issue is just getting the formula to recognize the value of the variable.

       

      The QS Server is 11.14.3.

       

      I am building a Calendar, but I'm also defining the Current and Last Fiscal Year as variables in my load script (so, I'm assuming each should only contain 1 value?):

      SET vFiscalYearStartMonth=10;

      Let vCurFiscalYear=YearName(Today(),0, $(vFiscalYearStartMonth));

      Let vLastFiscalYear=YearName(Today(),-1, $(vFiscalYearStartMonth));

       

      The variables seem to contain the proper values in the format YYYY-YYYY after the load script runs.

       

      The Asset_FiscalYear is loaded in my calendar as such:

      TempDate AS Asset_Date,

      ...

      YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS Asset_FiscalYear  // Fiscal Calendar Year

       

      The calendar table also seem to contain the FY in the format YYYY-YYYY.

       

      In my measure, this formula, with the hard-coded FY, seems to work and gives me the expected value:

      Sum({1<Asset_FiscalYear={'2017-2018'}>} [Total Revenue])

       

      However, I'm having a heck of a time trying to get that to use the value from the variable, even though all the formulas below are 'valid' as per the editor:

       

      This formula returns $0:

      Sum({<Asset_FiscalYear={"$(=$(vCurFiscalYear))"}>} [Total Revenue])

       

      This one gives me a total sum of ALL fiscal years (even if I swith the double quotes to single).

      Sum({1<Asset_FiscalYear={"=$(=$(vCurFiscalYear))"}>} [Total Revenue])

       

      And this one returns a Null:

      Sum({1<Asset_FiscalYear=(=$(vCurFiscalYear))}[Total Revenue])

       

      I've also tried using the Text() function to force the values to strings for comparison purposes, but no dice.

       

      Not sure what I'm doing wrong here... Any thoughts? TIA!