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

[johnw · Accepted solution]

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.

[#3]

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...

[Miguel_Angel_Baeyens]

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)

[johnw]

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)

[johnw]

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.

[#7]

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

[johnw]

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.

[#9]

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:

Sum({$<DAY={'$(=DAY)'}>} NUMBER_OF_DOWNLOADS)

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:

Sum({$<DAY={'$(=DAY-1)'}>} NUMBER_OF_DOWNLOADS)

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

[johnw]

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:

sum({<DAY={'$(=date(DAY-1,'MM/DD/YY'))'}>} NUMBER_OF_DOWNLOADS)

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.