ValueLists, Match and Pick

[MattSmart]

A look at how I used the ValueList, Match and Pick functions to improve a visualization.

[MattSmart]

One of the many things that I love about working here at Qlik, is that I am always learning something new. Let me show you something I learned recently while updating the CPG Market Analytics app. In the app I found this sheet:

This page is titled ‘How do we compare’ and shows five different gauges, each showing a different metric along with three ranges, and a reference line. The tick mark represents an average for the brand. So, the purpose of these visualizations is to show the values of each metric for Qlik_CPG, a manufacturer. I wanted to take these gauges to create a cleaner visualization, while also keeping and improving the capabilities of the originals by allowing users to compare the metrics of Qlik_CPG to other manufacturers.

So how can I do this?

After much debate and searching for inspiration, I decided upon a bullet chart. A bullet chart is kind of like a combination of a bar chart, and a gauge. It uses a bar within the chart to show progress toward a goal.

Unlike a bullet chart which requires both a measure and a dimension, our gauges only need a measure, but that is okay, we’ll be using a synthetic dimension to combine our gauges into a single dimension.

Here’s how:

We’ll start with the dimension. Bullet charts only allow for one dimension to be used, so we can use the ‘ValueList’ function to display multiple metric names. Our dimension will look like this:

The ValueList() function creates a synthetic dimension that allows you to define a list of values that Qlik treats like one dimension.  So basically, “Hey Qlik, I want to create a dimension, here are the values.”

So, with our dimension taken care of, we’ll move to the measure. This is what our measure looks like:

A little more complicated, but easily explainable. Our expression can be broken down into three functions, ValueList, Match and Pick.

ValueList() creates a synthetic dimension inside the chart with the labels we want to show (e.g., Attractive Packaging, Creative Advertisement, etc.).

Match() takes the current ValueList() value and compares it to our list of labels, returning the position of the first match (1 for the first item, 2 for the second, and so on).

Pick() then uses that numeric position to return the Nth measure expression from our list of measures. In other words, the order of labels in Match() is aligned with the order of expressions passed to Pick(): label #1 maps to measure #1, label #2 to measure #2, etc.

This lets us pair each label with its corresponding calculation. For example, when Qlik evaluates the Attractive Packaging dimension value, the expression resolves to Avg({$<Manufacturer={'Qlik_CPG'}>}[Attractive packaging]). Each ValueList item is evaluated in the same way, with Pick returning the appropriate measure for that label. When we place this into a bullet chart with targets, ranges, and styling, we get a compact visual of performance vs target across multiple KPIs.

Now keep in mind; that as per our expression, we are only showing these values for one Manufacturer, ‘Qlik_CPG’, we’re missing out on the functionality of being able to compare Qlik_CPG with the other Manufacturers. To keep this functionality, we’ll add another bullet chart with the other manufacturers to compare our Qlik CPG metrics to.

Here is an expression for the second bullet chart:

Great! Now our comparison chart shows every Manufacturer, and we can put it side by side with our Qlik_CPG chart to compare!

Maybe we want to be able to compare our Qlik_CPG metrics against the metrics of selected Manufacturers instead of all of them at once, adding a Filter Pane with the Manufacturers field will allow us to do just that.

To make things even better, we can go into our second bullet chart and make a dynamic title to show which manufacturers we’re showing in our chart. That expression for the Title will look like this:

This just says, “If we don’t have any selections for Manufacturer made, just title the chart ‘All Brands’ but if we do have a Manufacturer selected, name the chart those selections”.

So now we have taken our sheet, and gave it a whole new look that is much more concise and cleaner, while learning about the Pick, Match and ValueList functions.

Did you learn something through this article? Do you think the updated sheet looks better than the original? What would you do differently? How do you plan to use these functions in your own sheets? Leave your ideas in the comments below!