Tag: alternate_states

Highlighting Redux – Alternate States to the Rescue!

The year was 2010 … Katy Perry and Lady Gaga topped the charts. Gas was $4.00 a gallon. A beer in Los Angeles cost five bucks. And dynamic highlighting of dimensional values in QlikView charts (a.k.a. brushing) needed a data island. AND I did a post on this topic: Highlighting a “special” dimension in bar charts.

Fast forward to Summer 2013 … okay, not much has really changed in the bigger picture but WOW is it easier to do brushing in QlikView. Actually, this is not really a new technique because I’m going to employ alternate state to do this trick which has been around since version 11 first came out.

What’s wrong with the old method?

Recall the challenge with brushing is that we want to provide users the ability to dynamically focus on one dimension value (i.e. make a selection), but we don’t want the other dimension values to vanish. Prior to version 11, this could be accomplished by using a data island (i.e. a stand-alone table that does not associate to the rest of the data model) that will provide a dimension for the users to make selections. And this dimension is necessarily decoupled from the model because it is an island.

This approach works fine, but it’s cumbersome to have to create an island table for every dimension that may be used for brushing. How can we simplify that step…..

It just got easier – Altered States!

If we think for a moment how the old method worked – that is, we decoupled the brushed dimension from the chart … which we did via the data island. Alternate states is another way to decouple data entities, and the good news is that we do NOT need to build additional tables to do it.

We will use this technique to provide brushing capability to call-out a category for new construction spending. (Note: these data are publicly available from the US Census Bureau.)

So here is the high-level algorithm we need to employ:

  1. Create an alternate state – let’s call it “A”  (without the quote symbols)
  2. Create a list box for the user to select the brushed dimension – PUT THIS LIST BOX IN STATE A
  3. Create the chart – put the chart in the default state
  4. Fine-tune the bar color in cases where the chart dimension matches the brushed dimension

Let’s break this down step by step:

Step 1 – create an alternate state

The really, really easy step … go to the Document Properties and click on Alternate States to bring up the dialog box. Add a state and call it A. (Note: I also have a state called B in the image).

 

Step 2 – create a list box in State A

Create a list box on the chart dimension – [Category Desc] in our case. Be sure to assign the state to A in the General tab of the list box properties.

 

Step 3 – create the chart

Be sure that the chart is in the inherited state; i.e. the Default state (not the A state).

 

The chart itself is quite simple with a single dimension: [Category Desc].

 

And a single expression: sum(Spend)

 

Step 4 – fine-tune the bar color

The bar color can be grossly controlled by the setting on the Colors tab in the chart properties. But we can also finely control the color; i.e. by using a data-driven expression by expanding the expression (on the Expressions tab of properties) and manipulating the Background Color.

The expression we need to use here will compare the chart’s dimension [Category Desc] (which is in the Default state) to the value the user selects for [Category Desc] in the A state. If the two value match, then color that bar differently than the rest — this is the “brushing.”

if(match([Category Desc], only({A} [Category Desc])), $(vData2Color))

where vData2Color is the rgb() definition for an emphasis color (pull out your copy of Few’s “Information Dashboard Design” if you need inspiration.)

Notice that invoking the alternate state is as simple as putting the state’s name A in the set definition.

The result …

The final result is a chart that in which any of the bars can be dynamically “brushed” by the users.

 

More of this at Masters Summit – Europe!

This is one of the topics I’ll be covering in my Visualizations course at the Master Summit in Europe in October. Rob, Oleg, Barry and I will be sharing our techniques with students from the UK and Spain – hope you can join us!

 

Keep on Qlikin’

Bill

Shares

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

Shares

Share