Set Analysis & Alternate States – Quick Tip

I recently had my first opportunity to use Alternate States on a real project. As awesome as I think Alternate States are, I just haven’t had a chance to implement them before now.

As I was developing the expressions for my charts, I came across a predicament. And after some hand-wringing and head-scratching I realized it was a result of how I’ve been treating the set identifier, $.

As a reminder the set identifier $ is the default set. It represents the entire data model PLUS all user selections. Insofar as it is the default, it doesn’t need to be explicitly included in the expression. Meaning, when the $ is omitted in a set analysis expression, QlikView defaults to the default set. In other words,

sum({ $ } Sales) is equivalent to:   sum(Sales)

just as

sum({ $ <Year = {2010}> } Sales) is equivalent to:   sum({ <Year = {2010}> } Sales)

(notice the dollar-sign is absent in the 2nd expression)

Now, although the $ is not required, I have diligently included it in any and all expressions that use set analysis, rather than letting QlikView default it. My reasoning on this was to prefer the explicit over the implicit, i.e. leaving nothing to chance and more importantly, improving code understandability. And I’ve coached my students and clients to do the same.

“Always include the dollar-sign.”

B. Lay, said to dozens of students

…turns out that wasn’t such great advice!!

 

$ vs. Alternate States

So here comes the rub …

Without alternate states, the situation is simple: there is one and only one “default” state ($); namely the state of all user selections. But with alternate states defined, the concept of a “default” is no longer simple.

Instead of a single “default” state there are now:

• the document default state, $ – this is the state we’re familiar with prior to version 11
• a sheet state – which could be $, inherited from the document-level or one of the alternate states
• an object state – which could be $, inherited from the sheet-level or one of the alternate states

Additionally, any expression in any object can either inherit the state of the object or be controlled via set analysis. And this is the key point: sum({ $ } Sales) and  sum(Sales) now have potentially different meanings.

By hard-coding the $ in the set, we are explicitly choosing the good old-fashioned default state – all user selections. This ignores the alternate states. In fact, even if the object is assigned to an alternate state, by placing the $ in the set expression we are ignoring that alternate.

Contrast this to omitting the dollar-sign: sum(Sales) In this case we are accepting whatever state the object is in. That could be the default set $ … or it could be an alternate state, if indeed the object is assigned to an alternate state.

By omitting the dollar-sign we are allowing the set expression to “inherit” the object’s state.

My Mistake

Back to my issue (it’s all about me!) … I had created two identical charts, one in State A and the other in State B. And two groups of list boxes, one State A and the other in State B. Each chart had several expressions and included set analysis (for other calculation reasons). Here are the details (note I’m obscuring the client’s actual field names):

• Chart 1 – State A
• Expression 1: sum({$ <CurrYTD = {Y}>} Sales)
• Expression 2: sum({$ <CurrYTD = {Y}>} COGS)
• Expression 3: sum({$ <CurrYTD = {Y}>} Units)
• Expression 4: sum({$ <CurrYTD = {Y}>} #OrderCounter)

 

• Chart 1 – State B
• Expression 1: sum({$ <CurrYTD = {Y}>} Sales)
• Expression 2: sum({$ <CurrYTD = {Y}>} COGS)
• Expression 3: sum({$ <CurrYTD = {Y}>} Units)
• Expression 4: sum({$ <CurrYTD = {Y}>} #OrderCounter)

 

So this is following my old-fashioned approach of always including the set identifier $.

Hence, to my chagrin, when I clicked on values in my State A and State B list boxes, nothing changed. Again, because I had overwrote the object state by using a $ in my set analysis expressions. The fix for this was quite easy; simply removing the $ from the expressions.

• Chart 1 – State A
• Expression 1: sum({ <CurrYTD = {Y}>} Sales)
• Expression 2: sum({ <CurrYTD = {Y}>} COGS)
• Expression 3: sum({ <CurrYTD = {Y}>} Units)
• Expression 4: sum({ <CurrYTD = {Y}>} #OrderCounter)

 

• Chart 1 – State B
• Expression 1: sum({ <CurrYTD = {Y}>} Sales)
• Expression 2: sum({ <CurrYTD = {Y}>} COGS)
• Expression 3: sum({ <CurrYTD = {Y}>} Units)
• Expression 4: sum({ <CurrYTD = {Y}>} #OrderCounter)

 

The Takeaway

Well, despite the resiliency of old habits 🙂 I will now be omitting the $ in my set expressions.

 

Keep on Qlikin’

Bill