topic Re: Set Analysis & intersection (*=) in QlikView

Set Analysis & intersection (*=)

mikecrengland — Mon, 07 Oct 2013 20:57:07 GMT

Hello All -

I need some help understanding what I've done and hopefully some direction on a better way.

I have a straight table with a drill down group as the dimension. The fields in the drill down are Country - Segment - Business Name. (In our CRM, Segment is equivalent to Territory) I need to count the covered business and used:

= Count({<[Segment.Name] = {"**"}>} Distinct [Business.Key])

to count businesses with a Segment assigned. It works like a charm until I drill down to the actual segment. At that point, it doesn't filter to the actual segment. So, If I filter to Australia - 'Australia Segment 1', all of the Australia businesses are still returned in the table. (Instead of only Austrailia Segment 1, I get all segmented businesses in Australia)

If I change the expression to:

= Count({<[Segment.Name] *= {"**"}>} Distinct [Business.Key])

everything works as expected and I only get business from the segment that I've filtered to.

I guess my question is with the {"**"} ... I thought that meant count [Business.Key] if it has a segment. But I expected that it would still filter without the intersection set analysis. Any help would be much appreciated!

Mike

www.fortunecookiebi.com

Re: Set Analysis & intersection (*=)

stevedark — Mon, 07 Oct 2013 21:38:48 GMT

Hi Mike,

The default behaviour with Set Analysis is that you provide a selection to override the selection that is made in the UI. This can be really useful - but can lead to results that can be counter intuitive to users. By using the intersection code (*=) you don't override the selections made by the user - you further limit what they have selected to what the Set Analysis dictates. Generally this is a much better thing to be doing.

*= is a not particularly well documented feature of Set Analysis - but it is often the most useful way of doing things. Unfortunately, syntax highlighting is not properly aware of the syntax - and often highlights expressions in red when you try to use it.

- Steve

Re: Set Analysis & intersection (*=)

mikecrengland — Mon, 07 Oct 2013 22:19:03 GMT

Thanks, Steve!

One more question around the {"**"} modifier... Is that the right way to do 'not null'?

Re: Set Analysis & intersection (*=)

stevedark — Tue, 08 Oct 2013 08:05:12 GMT

Hi Mike,

It is indeed, except you should only need the one *. If this is a field you use for selections a lot you might want to consider replacing the nulls out in the load script, eg:

if(isnull([Segment.Name]), 'No Segment', [Segment.Name]) as [Segment.Name],

That means you can code for it and select it (or exclude it) more easily.

Cheers,

Steve

http://www.quickintelligence.co.uk/