A Better Show Frequency

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.

 count(DISTINCT OrderID)

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.

 count({<Customer=>} DISTINCT OrderID)

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,

    & repeat(chr(160),8)
    count({<Customer=>}DISTINCT OrderID)



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.



9 thoughts on “A Better Show Frequency”

  1. The Qlik Sense field as expression is a very cool concept. Won’t it also populate your Current Selections with your formula? I would consider that a big UX limitation to consider as well before using it.

    1. Speros,
      My experience is that the Qlik Sense current selections will appear just as you would want: Customer 3 of 300. Selections made against a formula generated list are made in the underlying associated Dimensions values.

  2. On Speros’s point my experience in Sense is a formula-generated list will show the whole formula in the current selections. I’ll have to check that one…

  3. Can you do a similar thing to fields added in a Multi Box, as we often use these for our filter selections instead of List boxes to conserve space in the dashboard?

    1. Dan, You can use Expressions to create fields in Multi-Boxes as in the Sense example above. Be sure to use the aggr() so selections get applied directly to the correct field.

  4. Is it also possible to sort the values by the count (or sum) so that I can see the biggest one first.

Leave a Reply

Your email address will not be published.