Set Analysis ignore all selections

[jcampbell474]

I'm trying to create an expression for a chart (straight table) that displays a value for the previous year to date.  Since dates change, I have variables to handle them - the start and stop dates.  I've tried several different examples of set analysis to ignore selections, ($,1), but the sum always changes when I select a different year, month.  How can I make a variable calculate the previous YTD without changing based on what dates the user selects?  Essentially, 'Isolate' the field...

The date format is YearMonth (201505).  A variable contains the start (201501) and another contains the stop (201505).

Creating fields for the variables:

Left(max(FiscalYearMonth),4)&'01' as cFYMStart,

max(FiscalYearMonth) as cFYMStop,

Left(max(FiscalYearMonth),4)-1&'01' as pFYMStart,

Left(max(FiscalYearMonth),4)-1&'12' as pFYMStop,

max(FiscalYearMonth)-100 as pmaxFYM

Resident Fact;

Here are the variables:

Let vcFYMStart = num(peek('cFYMStart'));

Let vcFYMStop = num(peek('cFYMStop'));

Let vpFYMStart = num(peek('pFYMStart'));

Let vpFYMStop = num(peek('pFYMStop'));

Let vpmaxFYM = num(peek('pmaxFYM'));

Here is one example of the Set Analysis I've tried:

=Sum({$<FiscalYearMonth={">=$(vcFYMStart) <=$(vcFYMStop)"}>} sold_cnt)

Additionally, I might add a couple of more views - Last 4-weeks (rolling), last 4-weeks previous year.  I'll need them to be static, too.

Is there a way to use Set Analysis so that I can calculate these numbers without them changing due to a different date being selected?

Thanks in advance!

[sunny_talwar · Accepted solution]

Have you tried this already?

=Sum({1<FiscalYearMonth={">=$(vcFYMStart) <=$(vcFYMStop)"}>} sold_cnt)

or

=Sum({$<FiscalYearMonth={">=$(vcFYMStart) <=$(vcFYMStop)"}, Year, Month>} sold_cnt)

[Gysbert_Wassenaar]

This expression should ignore all selections and select FiscalYearMonth values that match the interval defined by your variables:

=Sum({1<FiscalYearMonth={">=$(vcFYMStart) <=$(vcFYMStop)"}>} sold_cnt)

[jcampbell474]

Thank you both for the help.  I had tried those and thought they were wrong, but just figured out what the issue was.

I'm actually using: =Sum({$<FiscalYearMonthBound={">=$(vcFYMStart) <=$(vcFYMStop)"}>} sold_cnt).  Copied from the wrong text object in my initial post.  The biggest difference is FiscalYearMonthBound was being used instead of FiscalYearMonth.

Users select date (ranges) using FiscalYearMonth, which was changing the sum because it isn't in the Set Analysis.  I reloaded the data with FiscalYearBound reanamed FiscalYearMonth and updated the Set Analysis, now it works.

I had previously used $ and 1 but it just wouldn't work.  Thought I was losing my mind.  Happens every time - a problem in the load script causes me to spend way too much time troubleshooting/validating and it turns out to be something simple.

Again, I really, really appreciate the assistance!