topic Re: Set Analysis ignore all selections in QlikView

Set Analysis ignore all selections

jcampbell474 — Fri, 13 May 2016 16:15:29 GMT

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!

Re: Set Analysis ignore all selections

sunny_talwar — Fri, 13 May 2016 16:18:43 GMT

Have you tried this already?

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

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

Re: Set Analysis ignore all selections

Gysbert_Wassenaar — Fri, 13 May 2016 16:20:06 GMT

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)

Re: Set Analysis ignore all selections

jcampbell474 — Fri, 13 May 2016 18:48:41 GMT

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!