Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Set Analysis ignore all selections

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!

1 Solution

Accepted Solutions
sunny_talwar

Have you tried this already?

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

or

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

View solution in original post

3 Replies
sunny_talwar

Have you tried this already?

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

or

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

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand
jcampbell474
Creator III
Creator III
Author

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!