How to Reference a Variable in a Set Expression

[Anonymous]

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!

[johanlindell]

Have you tried Sum({<Asset_FiscalYear={'$(vCurFiscalYear)'}>} [Total Revenue])

The variable should contain the value 2017-2018. If you evaluate it it should lead to -1.

[sunny_talwar]

How about this

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

[Anonymous]

Thanks! That seems to give the right value, though for the life of me I don't see how I haven't tried that particular format before, especially since it's basically a substitution of '$(vCurFiscalYear)' for the hard-coded FY value of '2017-2018'.

But, the value changes if I make selections in my App. I tweaked your formula to:

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

This seems to give me Current Year Total Revenue regardless of any other selection.

Thanks again!

[johanlindell · Accepted solution]

Hi again,

Yes that would give you thiw year.

If you want to make it more dynamic, like current selected year and previous year you should be able to do something like

Sum({<Asset_FiscalYear={'$(=YearName ({<Date = {"<=$(=Today ())"}>} Today(),0, $(vFiscalYearStartMonth)))'}>} [Total Revenue])

and

Sum({<Asset_FiscalYear={'$(=YearName ({<Date = {"<=$(=Today ())"}>} Today(),-1, $(vFiscalYearStartMonth)))'}>} [Total Revenue])

This should give the current fiscal year of your selection and the previous year. I added a Date limitation that you should not look a head of today. If your calendar stretches beyond your current fiscal year you will get the last fiscal year and not the current.

.

[Anonymous]

I was actually thinking of doing something like that, but making the Date Offset of the YearName function a variable itself, but that's for v2. Right now, I only have 2 full years of data, so no worries yet.

Thanks!