The Third Format

In QV number formats we can use specify different patterns for positive and negative values. The two patterns are separated by a semicolon like this:  positive;negative

#,##0;-#,##0

Did you know a third pattern can be specified? It’s used for zero values.

#,##0;-#,##0;–

Using the above format zero displays as a double hyphen “–“.  Entering nothing after the second semicolon will result in zero being displayed as a blank.

The Qlikview Reference Guide appears to make no mention of the second and third formats. However, the formats look like Excel custom formats. Using that documentation, you’ll see that text may be used to create interesting formats like:

+#,##0.00% Increase;-#,##0.00% Decrease;No Change

Which when combined with Visual Cues can produce chart output like:

-Rob

Share

11 thoughts on “The Third Format”

  1. Good evening Rob,
    on QlikSense June 2018 if we use text (or visual cues) in custom format pattern the result of:
    +#,##0.00% Increase;-#,##0.00% Decrease;No Change

    is different from the result without text.

    +#,##0.00%;-#,##0.00%

    It seems that QlikSense needs “%” as last character of the custom format to correcly show the number formatting.

  2. Yes, the newer versions of QS and QV require a % as the last character to produce the auto % * 100 thing. As a workaround you can either multiply by 100 in the expression or add a % to the end of the format pattern, which gives a less pretty result.

    +#,##0.00 Increase%;-#,##0.00 Decrease%

Leave a Reply

Your email address will not be published. Required fields are marked *