QlikView – Formatting Expressions in List Boxes

Business Intelligence, QlikView, Uncategorized

When adding an expression to a list box, one of the common complaints is that the numbers displayed are devoid of any number formatting.  This is especially frustrating when displaying large numbers without commas to ease readability.

What most beginning report developers try is to format the values with the Number tools in the list box’s properties.

This fails to produce the expected results.  The reason why is the Number controls will only manipulate numbers that are contained in the selected field’s data; not additional expressions you add to the list box.

In order to apply number formatting to the expression results, the expression must be nested within a NUM function.  The NUM function will take the result of the expression and apply a number style.

EXAMPLE

Our original expression appears as follows:

Sum ([# Transported Passengers])

The revised, nested function will now appear as:

NUM(Sum ([# Transported Passengers]), ‘#,##0’)

If we open the Expression Editor, we can see the formula in its entirety.

The “#,##0” number format creates what most of us know as a “comma style” with no decimal place precision.

This produces the much desired results.