Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The image above shows my current display in a chart, currently setup as Expression Default in the Chart Properties (Number tab). I would like to only show up to two decimal places when the decimal is not 0. Ideally I would like to see:
3123.89
31911.27
450
72.42
1149.38
19468.14
471.25
14882.25
10428.75
14006.5
If I try to force the format pattern "Fixed to 2 decimals", it always shows .00 on the whole numbers and .#0 on the values that have only one decimal place. Some of the decimals are very large, which is why I want to only show 2 decimal places. If I use the round() function, it automatically tacks on two decimal places, even on whole numbers, so I'm still seeing .00 on values like 450 above.
Is it possible in Qlik to only show a certain number of decimal places, but only when they are non-zero?
Thanks in advance!
Sunny, I think you can make it even simpler. The idea is to set the Number format to Expression Default, and use that one whenever the fraction has 2 digits or less. More digits will use a num() with a specific number format string. This translates into something like:
=IF(Len(Trim(SubField(NumberField,'.',2))) > 2,Num(NumberField,'#0.00'),NumberField)
It even works in a text box that has no selection for expression default. Try it with a variable instead of NumberField and set the variable to different numbers.
Try this may be:
=If(Len(Trim(SubField(NumberField, '.', 2))) > 2, Num(NumberField, '#,##0.00'),
If(Len(Trim(SubField(NumberField, '.', 2))) = 1, Num(NumberField, '#,##0.0'), Num(NumberField, '#,##0')))
Sunny, I think you can make it even simpler. The idea is to set the Number format to Expression Default, and use that one whenever the fraction has 2 digits or less. More digits will use a num() with a specific number format string. This translates into something like:
=IF(Len(Trim(SubField(NumberField,'.',2))) > 2,Num(NumberField,'#0.00'),NumberField)
It even works in a text box that has no selection for expression default. Try it with a variable instead of NumberField and set the variable to different numbers.
Make sense