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
Is this in the documentation? I can’t believe I missed this over the last five years.
I haven’t found anything in the Ref Guide or Help about the second and third formats.
I too have managed to miss this feature, until now. Thanks again Rob!
Regarding showing direction in the format this post on using Unicode by Arturo Muñoz is worth a read:
http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/13/replacing-images-with-geometric-shapes-as-visual-clues
Rob, I noticed a strange behavior when I tried to use “+/- 0” as third parameter.
Please see my discussion:
http://community.qlik.com/thread/114222
Do you have an idea how to achieve that?
You can also copy and paste arrow images and many others from Windows Character Map, and use them in the excel type formats
How can Qlikview just overlook stuff like this? Doesn’t make it seem like a very mature tool no?
This is a great tidbit that I had no idea existed, yet will be very useful. Thanks for the insight, Rob.
Is there a way to color only the shape and not the value?
You can use the background color property to color the shape.
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.
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%