Tag: stephenfew

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

The Factoid – Text Boxes All Dressed-Up

Sometimes the simplest tasks are most challenging. Take for example the need to express a single, scalar number, such as the measurement of a KPI. One number, no dimensions, no time trend…simple, solitary…scary.

The trick is to NOT to overdo it. Don’t try to force a chart or speedometer or hygrometer or any other overblown visualization to do this job. In fact, the simplest way to express this result is probably just to state it, i.e. literally display the value as text.

A call center KPI example

Let’s consider an example from a call center. A key metric for the call center is call resolution time. This particular call center has a target resolution of five minutes. Here are some poor ways to visualize this metric (peruse any of Stephen Few’s writings if you’re not sure why):

If I had to choose one of these representations, the thermometer (far left) is the least offensive, but it consumes far too much space and the added context of seeing the level of “mercury” in the 100% gauge doesn’t really add much value.

Abandoning any of these visualizations, I opt instead for a simple text box:

The Factoid – adding context to the metric

To add some context to the metric’s value, we can utilize the the Text and the Caption areas of the text box.

By taking advantage of dynamic expressions in both the text and caption areas, you can build elaborate displays that combine both text and numerics. Be sure to spend some time on formatting the numbers. Functions like round(), num(), date() and the concatenation operator & (ampersand) are essentials.

Here is the code snippet for the caption:

=round(sum({$<[Affects SLA?] = {"-1"}>} [My Metric]), 0.001) * 100 & ' %'

And the text code:

='... of calls were resolved within ' & chr(10) & $(varCallResolutionTarget) &' min.'

where I have attempted to variable-ize all “moving pieces.”

The final result should look something like this:

I’ve coined this type of text box as a “factoid.” (“Unifact” sounds more representative, but a little too nerdy 🙂 ). Maybe QlikView will object-ize this and call it a QlikFact!

So to reiterate, don’t try to build a multi-dimensional visualization when you’re only trying to express a single number. I’ve found the factoid to be particularly useful when expressing KPI values, especially when there are well-known, impacting SLAs established and business users are keen to see performance numbers.

Keep on Qlik-in’
Bill

Shares

Color Mixology 101 & Heat Maps

I used a great QV feature for the first time this week – the Color Mix Wizard. Insofar as it is indeed a Wizard, there’s not a lot you need to know to use it, but I’ll walk through the steps I took…

The Grid Chart

The objective of this exercise was to come up with a visualization for looking at the number of survey respondents based on their answers to two different questions. My first attempt was to use a scatter plot, but that failed miserably since my dimensions (the x- and y-axes) were not continuous numbers, but rather ordinal values. Even when I encoded those ordinals as integers, the results were not what I was looking for.

SO…enter the grid chart.

Having worked through this example helped me clearly articulate the use case for the grid chart – basically, it is a version of a scatter chart that works for categorical values, be they nominal or ordinal. Nominal values are labels like “US, UK, FR” or “Finance, Sales, Operations.” Ordinal values are also categories, but can be arranged in a ranked order like “Under 25, 25-30, 30-35”. (for more info check out this Wikipedia article Level of measurement)

Here is my grid chart with two dimensions and bare-bones formatting:

Try as I might, I could find no simple way to make each horizontal row the same color…sigh…

Grid Meets Heat

So that grid chart tells me relatively how many people responded to each combination of question 1 and question 2. And it also provides some correlation information – for example, there are a large number of respondents falling into x-category: “18-25,” y-category: “4-6 hours.” This information is useful in and of itself, but I also wanted to compare it to their responses to a third question – a simple YES/NO question.

I could have opted for the pie chart style (from the Style tab) … but I’m already breaking Stephen Few rules by encoding magnitude as the radii of the bubbles 🙂 … best not make it worse with pies. Moreover, I personally don’t care for the aesthetic of the pie grid. Regardless, had I chosen this route, I could have simply used the third question as a third dimension and the pie chart would have proportioned two “slices” of pie – one for YES and one for NO. And included a legend too.

Instead, I decided to use a varying color saturation on grid bubbles. Since this will require a single number (vs. two categorical values – YES/NO) I needed a way to encode the survey responses to a single number. Hence I chose to take the ratio of YES responses (i.e. # of YES responses divided by # of ALL responses) which results in a floating point number between 0 and 1.0.

Now with a single number I can use it as a parameter to control color saturation. For example – I could simply use it as the alpha value (multiplying by 255 or similar), but I wanted finer control … especially at the “light” (faint) end of the scale. This is where the Color Wizard comes in!!

The Wizard (cue Black Sabbath)

First of all, it’s not obvious where to find this wizard…

  1. Expand the properties on the expression
  2. Click on Background Color to open the Expression Editor
  3. Go to the File Menu and you’ll see the Color Mix Wizard… menu item

Take some time to play with the wizard – it may take a few passes to get the effect you want. A couple of key parameters to explore:

Enhanced Colors – depending on the distribution of your data you may or may not want to select this option. It’s effect is analogous to a stereo enhancer for audio in that it takes the “middle” and pushes it out to both “sides.” Which in our case means taking the middle values and pushing them more towards to the upper and lower ends of the colors you’ve chosen.

Value Saturation (upper/lower) – again, your use case will dictate how to set this. If your data has long tails, you may want to set these limits so they group the outliers into a common color.

The Color Wizard will generate a piece of code that should look something like this:

=ColorMix1 ((rangemin(.8,rangemax([Legend Value] ,.1))-.1)/(.8-.1), ARGB(255, 242, 242, 242), ARGB(255, 128, 0, 0))

You can identify the Value Saturation range values (0.1 and 0.8) and the RGB values for the colors, so if you need to tweak the expression, you can do it directly on the code, instead of re-running the wizard.

NOTE: if you use Enhanced Colors you’ll get a much lengthier piece of code!

So now I have a true heat map, with the darker red bubbles indicating more YES responses to Question 3:

Finishing Touch – The Legend

Finally, it would be nice to have a more quantitative explanation of the heat levels besides the text: “darker means more YES responses.” So, a simple legend can be easily implemented in a straight table chart. This table will have just three columns:

  • the legend label – like “> 10%”
  • the legend value – 0.10, 0.20, etc.
  • a numeric expression (required to use the Chart object)

The expression really doesn’t matter because we’ll “white” it out (i.e. white text on white background).

Here is the load statement for the inline table:

LegendSaturation:

LOAD * INLINE [

Legend Label, Legend Value

< 10%, 0.1

20%, 0.2

30%, 0.3

40%, 0.4

etc...

 

];

After reloading the script, create a Chart object and select the Straight Table option. (I like Style = Table 1 for this example.) Choose Legend Label and Legend Value as the two dimensions. The expression column doesn’t matter, you can use something simple like only([Legend Value]) or sum([Legend Value]).

Now apply the colors…

  1. Expand the formatting options on the Legend Value dimension and edit the Background Color – you can either use the Color Mix Wizard again, or easier still, just copy the Expression from the Grid Chart object.
  2. Edit the Text Color option and paste in the same color expression from Background Color (this will render the text “invisible”)
  3. Go to the Expressions tab and expand the formatting options on the metric and edit the Text Color expression setting it to white() (assuming you’re using a white background)

That should do it! Resize the columns a little bit to balance the shape and you should have something that looks like this:

We Have Arrived

Finally, putting it all together:

Shares

Highlighting a “special” dimension in bar charts

A great way to provide your end-users added insight from a bar chart is to highlight a special dimension value.

What is a “special” dimension value?? For example, if you’re reporting on market share between your company and competitors, you should highlight “Our Company” on the chart. Stephen Few has a nice example on this topic in “Information Dashboard Design,” Chapter 6.

Another example would be if you’re a Product Manager and are looking at sales figures across various products in the line. By highlighting the specific product that you manage you can see where it stacks up against the rest … at a glance.

Imagine we really want to focus on Product = Alpha, and let’s highlight it with a saturated red bar:

So, naturally we need to figure out how to do this in QlikView, right?!

Controlling Background Color

Fortunately there is a nifty feature in the Expression Properties of the Chart object that allows you to dynamically control the Background Color of the bars. And because it itself is an expression you can use if-then logic or any other expression you can conjure.

Here I’ve chosen to use the red() function, but of course you can use any similar color function or rgb().

So this solution is just fine, except it requires that the dimension of interest be hard-coded into the expression in the chart properties. That may be okay for static analysis (like the market share example above – our company vs. our competitors). But for something that could change frequently (like Product) it would be a much more sustainable solution to be able to dynamically control the dimension of interest.

Dynamically controlling the dimension of interest

One way to make the dimension of interest dynamic is to store it in a variable. And then drop an Input Box object on the page. In this example we’ll call the variable varHighlightedProduct.

Now with a simple change to the Background Color expression –>

if(Product = $(varHighlightedProduct), red(255))

we can use an input box to control the highlighted dimension.

Another way to dynamically control the dimension of interest

The variable method works well enough, but changing the value in the input box requires a little bit of typing. Wouldn’t it be easier if there was a list box object in which the end-user could just select the dimension value of interest?? (answer = “YES”)

To build a separate list box we obviously need a data field. That can be easily accomplished with a resident load. Let’s suppose the sales data are stored in a table MySales, and the dimension field for product name is simply called Product. Then our load statement looks like:

HighlightedProduct:

LOAD DISTINCT Product AS [Highlighted Product] RESIDENT MySales;

This will load the unique values of Product into a table called HighlightedProduct with one field, [Highlighted Product].

Next, modify the Background Color expression so that it’s comparing against the selection in [Highlighted Product] –>

if(Product = [Highlighted Product], red(255)).

(You could get fancier with this code…allow multiple selections, restrict the list box to a single value, etc.)

So now we have a very convenient way for the end-user (the Product Manager in this example) to switch the dimension of interest using a simple list box.

I hope you find this simple example useful. Let me know if you have other ideas around this topic!

Shares

QlikView version 10 – coming soon….

I finally saw the demo of QlikView version 10 this week, presented by Anthony Deighton of QlikTech. And I am chomping at the proverbial bit to get my hands on it. The official launch date is 10/10/10 (cute).

A number of impressive new features are included, and a few in particular stood out

  • Metrics in list boxes – extending the existing feature of allowing a frequency metric in a list box, you can now put in any metric (presumably through the Expression Editor which wasn’t shown). You can even do a microchart. I’m not entirely sure how this is conceptually different from the existing Straight Table object…perhaps the list boxes quicker to configure? At a minimum though, I could this as being a handy feature when profiling new data sources (I’ll take this up in a future blog topic.)
  • Container objects – a great way to better manage screen “real estate.” This will get around the hassle of setting up icon minimize/maximize cycles. And moreover I think the selection options presented in the Container object will be more obvious to business users who are new to any given application.
  • Mekko charts – hmmm… I’m not sure how useful these will be. I mean, I get the fact that now another “degree of freedom” can be encoded in a two-dimensional bar/column chart (by varying the bar width with an additional expression). I get it, bravo, well done. …But, I’m just not sure how easy they will be for the “average bear” to interpret. I’ll be interested to put these in front of some business users and get their feedback.
  • Extensions are a new feature that, well…extend the existing functionality. One of the extensions I could see using is the Gantt chart. The other ones I saw looked like more “eye candy” than anything. I’m sure Stephen Few will have some priceless and spot-on comments on these :)

In addition to gobs of GUI goodness, there are also some impressive changes coming to the ETL scripting and server administration:

  • Multi-threaded reloads – yay! Now I can get my money’s worth on that 16-core processor!
  • Easier user administration in QV Server – my admin will love me!
  • Version control … although, before getting too excited on deploying this, it will certainly need a test drive (check out the commentary at Guerrilla BI).
  • Other improvements to the scripting tool – I heard “type-ahead completion” mentioned, but didn’t catch whether that was to be in v.10 or not

SO…get ready…10/10/10 !

Shares

Share