The One

I don't need to know you. "You" only need to know "me". I will be The One!

Jet Li. China's closest thing to Chuck Norris. Good thing for Jet Li that I'm not Chinese because then I'd be China's closest thing to Chuck Norris. Did you know that Chuck once ran around the world so fast that he hit himself in the back of the head? That's kind of how I felt when it dawned on me how to use the '1' in set analysis when you really just want 'one' of your list boxes to act on an object. The fog parted like butter cutting through a hot knife. Like ghosts sitting around a campfire telling Chuck Norris stories, put on your brave face and follow along...

First, a little groundwork. Set Analysis is conceptually similar to a selection in a list box. It provides a method of defining groups (sets) of information that are independent of the current selections. Using a '1' as your identifier essentially ignores any of the user's selections.

     Count ( { 1 < [Field] = {'Values'} > } [Field2] )

The problem arises when we have an object where we might want to freeze except, for example, the rep segment. Currently, we would use set analysis to ignore individual fields by using an 'empty' = sign:

     Count ( { < [Field] = , ... [OtherFields] =  > } [Field2] )

That's fine if you only have to ignore a couple of fields, but what to do if there are dozens? Well, let's do it my new way. Pick up a rock, get behind a boulder, and the minute its head is in view, hit it with the rock. (My new way is not very sportsman-like.)

Back to the groundwork. Think about this part of your set analysis, it's ponderous, man:

     [Field] = {'Values'}

You are telling QlikView to only run your expression where [Field] has the following list of items {'Values'}.

The trick is to use your list box to fake {'Values'}. Inside the curly brackets, you can use an expression instead of actual values. Typical ways include between dates and variables. We are going to use the concatenate function.

     Concat (Distinct Chr(39) & [Segment Name] & Chr(39),',')

In your set analysis, you need to use $-sign expansion. It would look something like this:

     =Count ({1<[Segment Name] = {$(=Concat (Distinct Chr(39) & [Segment Name] & Chr(39),','))}
     >} [Store ID])

Please note a few very important things about this expression:

• Chr(39) is the ASCII code for a tick mark; remember that the tick mark denotes a string
• The $-sign expansion IS NOT wrapped in tick marks, since we are including them in the Concat function
• This expression doesn't work like you think it should

The problem with the Concat function is that it concatenates either what's selected or what possible to select. What that means is that if you don't have a [Segment Name] selected, but you do have a [District] selected, the Concat function will return the [Segment Name]s that belong to that [District].

And that's not what we want.

To fix it, we'll add an 'If'' in the modifier.

     Sum({1<[Segment Name] = {$(=If(GetSelectedCount([Segment Name]) = 0,
             Chr(39) & '*' & Chr(39),
             Concat (Distinct Chr(39) & [Segment Name] & Chr(39),',')))}
      >}[Net Price])

Again, some explanation:

• GetSelectedCount counts the number of selections actually made it the [Segment Name] list box
• If the count equals 0, then return Chr(39) & '*' & Chr(39), which gives you '*'; that makes the modifier look like [Segment Name] = {'*'}
• If the count is greater than 0 then return Concat (Distinct Chr(39) & [Segment Name] & Chr(39),','), which give you a comma separated, tick-mark-wrapped, list of the selected segments; that makes the modifier look like [Segment Name] = {'Boston','Chicago'}

Now for 'The One', the video. Make sure to turn up your speakers, because it's ponderous, man.

This is the weird part of my dream, where I can tap dance but can only do it while wearing golf shoes.