Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
anothergreg
Contributor III
Contributor III

Format pattern with conditional decimal

qlik.JPG

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!

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

View solution in original post

3 Replies
sunny_talwar

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

sunny_talwar

Make sense