Better List Boxes

You know the kind of guy who does nothing but bad things, and then wonders why his life sucks? Well... that was me. Every time something good happened to me, something bad was always waiting around the corner. Karma. That's when I realized I had to change. So, I made a list of every bad report I've ever done and, one by one I'm going to make up for all my mistakes. I'm just trying to be a better analyst. My name is Mike.

# 48,408,730 on my list is creating boring List Boxes. Every dashboard out there has something like this:

While it's a useful tool and let's your users filter and experience Qlikview's associative power, it's really boring. And to top it off, that bold, blue caption bar screams, "I'm important!" As all faithful FortuneCookieBI fans know, screaming is only for actually important things, not for captions. See what I did there?

The first thing we're going to do, is fix that caption. Light grey background with dark grey text is a good start. It's very readable, but still subtle. Notice also that the horizontal scroll bar is gone.

Better, right? Next, think about the information that the object shows. It's a list of retailers. Important, but not terribly helpful. An easy upgrade is to add an expression to give some kind of context. In this case, let's add a count. In the List Box Properties, navigate to the Expressions tab and click Add.

For simplicity's sake, we will add a count of the Trade Partners. Now your list box adds some useful information. It's even sorted by the number of Trade Partners (don't forget to change the sort order):

Instead of an alphabetically sorted list, now you know which is most important. But a list of numbers are kind of boring. A more appealing way would be to display the expression as a bar chart.

First, change the Representation to 'Linear Gauge' and click 'Gauge Settings':

Follow these steps in the Settings dialog:

1. Make sure that you only have one Segment
2. Change Mode to Fill to Value
3. Uncheck Show Scale
4. Uncheck Hide Segment  Boundaries and check Hide Gauge Outlines
5. Change the color to something that won't hurt my eyes
6. Give it a maximum value that corresponds to your expression

# 6,464 on my list is saying things with authority so that I'm not questioned. So I guess I should explain # 6 above.

Logically, you want the maximum value for your gauge to be the largest count of Trade Partners. So you might try something like:

     = Max ( Count ( [Trade Partner] ) )

But Qlikview, at it's most base level, is just an aggregation tool. It aggregates data at whatever level you tell it to. In this case, you've told it to count the Trade Partners in the entire document and then give you the maximum value. In this particular case, 7,300. Two problems:

• The expression doesn't work
• The maximum value that you actually want is 1,844 (if no filters are applied)

The answer is to aggregate the count on the Trade Partner field:

     = Max (Aggr (Count([Source Trade Partner]), [Source Trade Partner]))

In plain English, the above expression says "Count the number of times [Source Trade Partner] shows up, grouped by [Source Trade Partner].  Maybe not so plain, but you get the idea because you're a smart  FortuneCookieBI fan. Now, isn't that Just What The Doctor Ordered?