topic Re: Format pattern with conditional decimal in QlikView

Format pattern with conditional decimal

anothergreg — Tue, 08 Dec 2015 21:38:24 GMT

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!

Re: Format pattern with conditional decimal

sunny_talwar — Tue, 08 Dec 2015 21:48:33 GMT

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')))

Re: Format pattern with conditional decimal

Peter_Cammaert — Tue, 08 Dec 2015 22:53:43 GMT

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.

Re: Format pattern with conditional decimal

sunny_talwar — Tue, 08 Dec 2015 23:57:08 GMT

Make sense