Set Analysis Filter Based on Variable Table Value

[#1]

Hi,

I need to setup a table that's filtered based values relative to a selected value on a page. For example, there is a table that contains the values 1 - 12. I have a filter setup where the end user can choose a value from that list. Once that value is selected, I have a table that needs to be filtered based on that value. One column is based on that value, the next is the value before the selection in the table, and the next is the value before that.

I need this filter to also be based on the current selections on the page.

I've very new to Set Analysis, and I'm under the gun from the boss to make this happen., so the more detailed explanation you can provide would be appreciated.

Thanks,

Scott

[#2]

Awesome! I used your last approach and it worked! You should write a book with all these tips! Is there a resource other than the User Forum where we can easily look for tips and techniques?

[johnw]

Glad it worked for you. As far as finding additional tips and techniques, I get quite a bit of information from looking at the help text and the reference manual. Rob Wunderlich put together a bunch of nice solutions to common problems in his QlikView Cookbook, available here:

http://robwunderlich.com/Download.html

You can find links to Blogs, Shared QlikViews and the Wiki at the top of the screen. Lots of good examples and discussion among those. And of course the forum is an excellent resource when you have a specific question you want answered.

[#4]

Great, thanks for the links.

One thing I noticed with these solutions - If I select March and April, the Average Headcount calculation picks up both March and Apri for April. However, for March it fails to pick up February's headcount number. How can I force the first month of the selections to also pick up the previous month?

[#5]

Sorry, I think I figured it out. I have the new AsOfMonth field and also my original corresponding Range field. I was selecting on Range. I created an Input Box for AsOfMonth and that seems to work fine.

[#6]

John,

I'm trying to apply this same methodology to my data set which is set up somewhat differently. Instead of having month represented by an integer 1-12, I use actual dates. I get a feed every month end and my variable is the last day of each month. I've loaded a master calendar and mapped my dates to be displayed as MMM YYYY (field is called CalendarMonthAndYear). What I'm trying to do is show a change in exposure by different dimensions in the selected month vs. the prior. I have no problem showing the values for the selected month, but am getting errors on the prior. I'm using set analysis to try and limit the variable to only the prior month. Here is the code I am using.

Normal 0 false false false MicrosoftInternetExplorer4 sum({<CalendarMonthAndYear={$(=Date(Monthend(CalendarMonthStart-1),'MMM YYYY'))}>} Exposure)

CalendarMonthStart is part of the master calendar and returns the first day of the month for whatever month end is selected.

I'm fairly new to QV and am thinking it's a date format issue, but any insight would be appreciated.

Regards,

Mike

[johnw]

I don't actually use numbers for months either. I use the month start date. The month end date is pretty much the same idea, so no problem there.

I'm not seeing anything that strikes me as a big issue, just little ones. First, there's no need for the monthend() function since the first day of a month minus one day is the last day of the previous month.

What might be causing a problem is if any OTHER fields in the calendar are selected. You haven't told the set analysis to ignore those other fields, so it would try to come up with the set where those fields have their value, and the month has the previous month's value, and it'll return a null set for that. So you'll probably want to tell it to ignore ALL of your calendar fields.

Oh! I see it. You're missing single quotes around the literal. Since it has a space in it, QlikView is going to insist that you put it in quotes in order to do the match. I try to always put my literals in single quotes, just to identify them as literals, even when they don't need it.

Put it all together, and you're looking at something like this, and I'm just making up your field names from the calendar table:

sum({<CalendarMonthAndYear={'$(=date(CalendarMonthStart-1,'MMM YYYY'))'}
,CalendarMonthStart= // means ignore this field's selections
,CalendarMonth=
,CalendarYear=
,CalendarDate=
,CalendarWeekAndYear=
>} Exposure)

[#8]

Thanks John. Everything's working great now.

[shumailh]

Dear John,

Why the below code is not working?

=sum({$<MonthName_ID = {$(=MonthName(MonthEnd(LoadDate -Day(LoadDate))))}>} bal)

Shumail

[shumailh]

done thanks

=sum({$<MonthName_ID = {'$(=MonthName(MonthEnd(LoadDate -Day(LoadDate))))'}>} bal)