28 Replies Latest reply: Jul 10, 2011 8:23 AM by Shumail Hussain

# Set Analysis Filter Based on Variable Table Value

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

• ###### Set Analysis Filter Based on Variable Table Value

See attached. User selects a month, and I show the sum of sales by customer for that month and the two previous. The expression are:

sum(Sales)
sum({<Month={\$(=Month-1)}>} Sales)
sum({<Month={\$(=Month-2)}>} Sales)

Hopefully the first is obvious. So we're looking at the second one. A set analysis expression like this MODIFIES the selected set of data. So although we've only selected data for Month 6, we're now modifying that. The Month={...} says that we're specifically overriding the Month field. The \$(=...) is called a dollar sign expansion. The expression inside is evaluated FIRST, before anything else happens, and then REPLACES what was there. So the very first thing this expression does is check the value of Month (which is 6), and then subtract 1 from it to get 5. This replaces the dollar sign expasion expression, so QlikView is then looking at this: sum({<Month={5}>} Sales). So by the time it overrides the main set of data (where Month=6), we've already used that month to calculate the set of data we want instead. And what we want instead here is to pretend that they selected Month 5 instead of month 6. So that column of data will pretend that they selected month 5 instead of month 6. And then hopefully the last expression is obvious. 6-2=4, so 4 is substituted, this overrides the Month for that column only, so it shows the sum of sales for Month 4.

While not an important piece for what you're trying to do, I'll also mention the set analysis expression in my "Raw Sales Data" chart, sum({1} Sales). The {1} says to ignore ALL selections and use ALL data. So that's how I can show the full set of raw data, even if you select customers and months.

• ###### Set Analysis Filter Based on Variable Table Value

Hi John !

How do you use the "dollar sign expression" with a dimension which is named with more than 1 word ?

For instance, if your dimension "Month" is in fact "My Month" ?

`sum({<[My Month]={\$(=My Month-1)}>} Sales)`

and

`sum({<[My Month]={\$(=[My Month]-1)}>} Sales)`

Seems not to work...

• ###### Set Analysis Filter Based on Variable Table Value

`nmartin wrote:For instance, if your dimension "Month" is in fact "My Month" ?`

I'd use (untested)

`sum({<[My Month]={"\$(=Month([My Month])-1)"}>} Sales)`

• ###### Set Analysis Filter Based on Variable Table Value

`Miguel A. Baeyens wrote:I'd use (untested)sum({<[My Month]={"\$(=Month([My Month])-1)"}>} Sales)`

That should work, but the double quotes specify a search string instead of a literal. Literals are either in single quotes, or out of quotes completely if they happen to have no whitespace. I should probably get in the habit of always putting literals in single quotes rather than using two separate syntaxes for the same thing. Also, [My Month] is already a month, so month([My Month]) does nothing. Oh, I'm wrong. I just tried it. The month() function actually prevents it from working.

• ###### Set Analysis Filter Based on Variable Table Value

`nmartin wrote:How do you use the "dollar sign expression" with a dimension which is named with more than 1 word ?sum({<[My Month]={\$(=[My Month]-1)}>} Sales)Seems not to work...`

Works just fine in my sample application. If it isn't working for you, syntax isn't the probem. It's the same rule as always - fields with whitespace need to be enclosed in quotes or brackets. There's nothing special about using set analysis that changes that requirement. Both of the following work correctly. See attached.

sum({<[My Month]={\$(=[My Month]-1)}>} Sales)
sum({<"My Month"={\$(="My Month"-2)}>} Sales)

• ###### Set Analysis Filter Based on Variable Table Value

Thanks, John. I'll give that a try. In the mean time, how would you navigate between records if the value isn't numeric? For example, I have data based on periods so that the value is P01, P02, P03, etc. For this field, I can't just subtract to get the previous value. I actually need to navigate to the previous record and retrieve the value.

Thanks,

Scott

• ###### Set Analysis Filter Based on Variable Table Value

Actually, for that field, you CAN just subtract to the the previous value. You just need to remove the P and then put it back on when you're done, or store a related field that doesn't have the P on it. You could have a related field which is the first day of each period, and subtract the number of days or months in the period. You could check for the maximum text value less than the selected value. You could handle it in the script without set analysis by creating a linkage table to connect a period to all of the records of the period with "Period Type"='Current', and to all of the records for the previous period with "Period Type"='Previous'. You could handle it in the script without set analysis by adding fields "Previous Value" and "Two Periods Ago Value". You could do accumulations in your script, though I wouldn't usually recommend it.

The list of possible solutions for all hypothetical problems dealing with summing data for previous values is LONG. The best solution is going to depend on the actual situation. If you have an actual, specific problem you're trying to solve, then perhaps we can give you an actual, specific solution. Otherwise, it could be a long time before someone accidentally gives you a solution that happens to match your actual requirement.

Perhaps your most general-purpose answer would be the "check for the maximum text value less than the selected value". Here's an example of that:

sum(Sales)
sum({<"My Month"={\$(=max({1} if("My Month"<\$(="My Month"),"My Month")))}>} Sales)
sum({<"My Month"={\$(=max({1} if("My Month"<\$(=max({1} if("My Month"<\$(="My Month"),"My Month"))),"My Month")))}>} Sales)

There's probably a more efficient way to write the expressions. And there's probably a better way to handle most practical examples.

• ###### Set Analysis Filter Based on Variable Table Value

Thanks for the tip, John. I can't quite get the command to work, and I can't figure out why. I'm using the following expression to get the selected days data:

I know I don't need to do all this and can just say Sum(NUMBER_OF_DOWNLOADS), but I tried this and actually got it to calculate correctly. So for the day before's data, I have this expression:

This returns a zero. I'm wondering if there is a DAY function or something that's throwing off the calculation.

Any insight you could provide would be appreciated.

Thanks,

Scott

• ###### Set Analysis Filter Based on Variable Table Value

It's possible that it's just a formatting problem. If DAY is anything other than an integer, then you need to format DAY-1 to match. For instance, if DAY is a date in format MM/DD/YY, you would need this:

You need to format it manually because unfortunately, QlikView does not appear smart enough to do a date comparison on dates, or to apply the right format to the list of values automatically. Instead, it seems to be doing some sort of text comparison of the two values, ending up with something like '03/15/09' vs. '39887'. So it says these aren't equal, even though 39887 is how 03/15/09 is stored internally. That's something I'd like to see get changed some day, but in the mean time, always consider the format of the data when doing set analysis.

Mind you, this isn't necessarily the problem, but it's my first guess.

• ###### Set Analysis Filter Based on Variable Table Value

Hey John. Thanks for the tip. I checked out the current format of the Day field, and it's M/D/YYY, so I tried the following expression:

Unfortunately, this is still returning a 0. That tells me that the expression is being calculated, but the Set Analysis isn't identifying a Day field that matched Day - 1. If you have any other ideas, I would love to hear them.

Thanks,

Scott

• ###### Set Analysis Filter Based on Variable Table Value

I take that back, John. I figured out that the actual format of the data is DD/MM/YYYY, so when I added that to the Set Analysis, it worked great. Thanks a lot for your help.

• ###### Set Analysis Filter Based on Variable Table Value

John,

I am doing something similar, but for monthly headcount and attrition analysis. This problem I'm running into is when multiple months are selected. If a single month is selected, your code works fine, but if several months are selected it doesn't. Performance Rating and YearMonth are dimensions in my report and I have an expression that calculates Average Headcount. The requirement is to have a month over month report based on several user selections. Have you come across anything like this? Thanks.

Art

• ###### Set Analysis Filter Based on Variable Table Value

Sets in set analysis are evaluted once for the entire chart, not once per row of the chart. Therefore, set analysis works fine when a single month is selected, but won't work if you have multiple months selected, and want to apply it to each month.

However, I'm unclear on your requirement. What is the definition of "Average Headcount" given a "Performance Rating" and a "Year Month"? It could be as simple as avg(Headcount), but I'm betting there's a lot more to it.

• ###### Set Analysis Filter Based on Variable Table Value

The requirement is to let the user select multiple months and dump the data directly to Excel. It is easier for her to do this once instead of 12 times. We would also be using the same logic to produce month over month graphs. I posted this last night:

For a Headcount and Attrition Analysis report, I need to calculate the Average Heacount for any given month as (current headcount + previous headcount)/2. I've created this expression to do that:

Once I add YearMonth as a Dimension, the above expression no longer works. The previous month's headcount is 0.

• ###### Set Analysis Filter Based on Variable Table Value

OK, so for each month, "Average Headcount" is the headcount for the previous month plus the headcount for the current month, divided by two. You want a user to be able to select, say, a 12 month time frame, and have a chart with all twelve months in it, and the "Average Headcount" for each month, correct?

What I suggest is the following table:

AsOfYearMonth, Type, YearMonth
May 2010, 2mo, May 2010
May 2010, 2mo, Apr 2010
Apr 2010, 2mo, Apr 2010
Apr 2010, 2mo, Mar 2010
etc.

Then your chart would be laid out as follows:

Dimension = AsOfYearMonth
Expression = sum({<[Employee_Non-Employee]={'Employee'},[Type]={'2mo'}>} TurnoverPopulationFlag)/2

And actually, if you don't have other similar requirements with different intervals, you can eliminate the Type field to simplify things. Most examples I see need a type field, so it's kind of habit for me by now, even though your example doesn't need it.

I suspect you could get the same numbers more easily by using the above() function, but I consider chart solutions like that to be less robust than data solutions, as simply changing the sort order, for instance, can break it.

• ###### Set Analysis Filter Based on Variable Table Value

Thanks John. I understand what you're doing here, but I'm struggling with how to implement it. Should I do an inline LOAD of the table you described? I'm not really sure how in my Load Script this should be done.

• ###### Set Analysis Filter Based on Variable Table Value

I'd probably do it like this for highest performance:

AsOf:
// Preceeding load to establish the "current month" of the two months you want
YearMonth
,YearMonth as AsOfYearMonth
,'2mo' as Type
;
// This is very fast compared to loading distinct values from a large table.
AUTOGENERATE 1
WHILE len(fieldvalue('YearMonth',iterno()))
;
// Concatenate back to itself adding one month to the AsOfYearMonth.
// Preceeding load removes the combination that falls outside of your existing date range.
CONCATENATE (AsOf)
WHERE AsOfYearMonth <= monthstart(today())
;
YearMonth
,Type
RESIDENT AsOf
;

But this would probably be more straightforward and extensible if performance isn't at a premium:

AsOf:
// Load every possible YearMonth from the MainTable.
RESIDENT MainTable
;
// Do it again with a left join, which will give us every possible combination of two YearMonths.
LEFT JOIN (AsOf)
RESIDENT AsOf
;
// Then inner join to eliminate combinations that don't meet our "two month" criterion.
INNER JOIN (AsOf)
RESIDENT AsOf
AND YearMonth <= AsOfYearMonth
;

Both are untested, so I don't guarantee correctness.

• ###### Set Analysis Filter Based on Variable Table Value

John,

I used solution #2 and it worked great! Thanks. I missed one thing though. I also have a Voluntary Term Count column. Average Headcount works perfectly. However, my Vol column is now cumulative. How do I display the count just for the month and not the sum for the month and previous month? Is there a function that will pick up the first or last instance?

• ###### Set Analysis Filter Based on Variable Table Value

One approach would be to replace "Voluntary Term Count" with if(YearMonth=AsOfYearMonth,"Voluntary Term Count"). A consistent data approach would be to concatenate a 'Current' Type to our new table for the current month only. Yet another approach would be to replace "Type" with "MonthsBack" like this:

AsOf:
// Load every possible YearMonth from the MainTable.
RESIDENT MainTable
;
// Do it again with a left join, which will give us every possible combination of two YearMonths.
LEFT JOIN (AsOf)
RESIDENT AsOf
;
// Then inner join to eliminate combinations that we don't need.
INNER JOIN (AsOf)
RESIDENT AsOf
AND YearMonth <= AsOfYearMonth
;

Then when you want both months, this goes in your set analysis expression:

MonthsBack={0,1}

And when you only want the specific month, you use this instead:

MonthsBack={0}

• ###### Set Analysis Filter Based on Variable Table Value

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?

• ###### Set Analysis Filter Based on Variable Table Value

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:

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.

• ###### Set Analysis Filter Based on Variable Table Value

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?

• ###### Set Analysis Filter Based on Variable Table Value

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.

• ###### Set Analysis Filter Based on Variable Table Value

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

• ###### Set Analysis Filter Based on Variable Table Value

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)

• ###### Set Analysis Filter Based on Variable Table Value

Thanks John. Everything's working great now.

• ###### Set Analysis Filter Based on Variable Table Value

Dear John,

Why the below code is not working?