I like using “Show Frequency” in a Listbox, but the feature suffers two drawbacks.

1. If the Field is in a Dimension table, the frequency — usually 1 — is not particularly useful to the user.
2. Excluded (gray) rows show a blank frequency value.

As an alternative to “Show Frequency”, we can use the Expression pane of the Listbox to provide a more meaningful number, such as count of orders.

num(
 count(DISTINCT OrderID)
,'#,##0')

Listbox Expressions don’t provide for a heading.   We can fake one by adding it the Listbox Title with enough spaces to align the heading.

The excluded values show zero. We can fix that by adding a Set to the expression. The Set should honor selections in other fields such as Product or Year, so we only need to ignore the selection in this field.

num(
 count({<Customer=>} DISTINCT OrderID)
,'#,##0')

Optionally, we can use the same expression as a Sort Expression if we want to sort by Order count.

There you have it.   A more useful Frequency for the QlikView Listbox.

The Qlik Sense Filter Pane does not provide an Expression property, but you can achieve a similar result in Qlik Sense by creating the Field as an expression,

aggr( 
    only({<Customer=>}Customer) 
    & repeat(chr(160),8)
    &num(
    count({<Customer=>}DISTINCT OrderID)
    ,'#,##0')
,Customer)

The “repeat(chr(160),8)” is a trick to insert 8 “non-breaking” spaces. A string of multiple regular ‘  ‘ spaces will display as only a single space when displayed in the browser.

You may want to place the numbers on the left side to make the numbers clearer.

And yes, you could dynamically calculate the number of spaces required to get multiple digits right-aligned.

Using  aggr() to create a Filter Box is a much “heavier calculation” than just using the Field. If you have a large application, test this technique for performance before deciding to use it there.

-Rob

Using dimensions on the X and Y axis and plotting measures at the intersection is a useful visualization. The out of the box solution in QlikView is the Grid chart.

The grid chart does have limited options for representing data.

The Pivot Table can be a more robust alternative.  A Pivot Table grid (Crosstable) is created by dragging one of the dimensions to the horizontal position.

Now we can represent the measures as a heatmap by coloring the background of the cells.  This is frequently a more effective visualization than circles,  .

We can also display numbers in the cells.

We can display multiple expressions per dimension.

Here I show both the number and a linear gauge allowing for clear comparisons between Regions. The user can swap the dimensions if desired.

You can mix colors and numbers to show interesting patterns. Here’s a map of average Sunrise/Sunset/Daylight for my home town.

Maybe it’s not a design winner, but I find it interesting.

We can blank some cells in the grid to make meaningful shapes, in this case a rough outline of the United States.

All of the examples shown can be downloaded here.

A few styling tips:

• Style format “Clean” is a good starting point for managing border lines and spacing.
• “Custom Format Cell” Border before/after (double line) can be used to create white space between rows as shown in the Minimum Wage example above.  Also, setting Text Size to other than the default 100% can be useful.

Do you have any examples of Pivot Grids to share?

-Rob

It’s been quite a while since my last post. Summer vacations, Masters Summits in New York and Copenhagen, a Luminary meetup in Lund. Lots of new input for blogging.

I’ve been testing the QV Version 12 Beta and am pleased to see  a few simple but useful enhancements in bar charts.

•  Multi-line horizontal legends on x-axis. Works in both vertical and horizontal orientation.
• “Plot Values Inside Segments” now honors scaling/symbols property from the Number pane,
•  New Presentation option: “Still Show Total on Top” when plotting Values in Segments.

These enhancements allow you to easily create charts like this example, even with values in  thousands or millions.

You will need to create your own line breaks in the dimension. There is no auto wrap yet.  Create a calculated dimension that replaces blanks with the newline char:

=replace(ProductName,' ', chr(10))

Another QV12 plus — those annoying false syntax errors in the expression editor have been cleaned up and the expression editor is my friend again.

I’m looking forward to discovering more new features in QV12 as my testing continues.

-Rob