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!