Round up to 2 decimals, but ONLY to Fractions, NOT to Integers - HOW?

[dmohanty]

Hi All,

I have a Text Box showing a value calculated by 2 variables. For example var A + var 2.

For  Id =100, var A + var B = 35

For  Id =200, var A + var B = 20.5

For  Id =300, var A + var B = 40.275

For  Id =400, var A + var B = 15.25

Requirement:

The result should be rounded up to 2 Decimal places, but only for values/results having 2 or more decimal places. I don't need ZERO after decimals.

For example, my expected output would be

For  Id =100, var A + var B = 35   (not 35.00)

For  Id =200, var A + var B = 20.5 (not 20.50)

For  Id =300, var A + var B = 40.28

For  Id =400, var A + var B = 15.25

I tried using NUM( 'var A + var B , '#.##') Can this be achieved in some other ways?

Regards!

[Anonymous]

Hi,

Try like this:

Num(var A+var 2),'##.00') ,It wil give you only two decimal points.

[Anonymous]

Hi,

Use round( x [ , base [ , offset ]]) . Find the screenshot it will help.

[sunny_talwar]

Try this:

=If(frac(FieldName*10) > 0, Num(FieldName, '#,##0.00'), If(frac(FieldName)>0, Num(FieldName, '#,##0.0'), Num(FieldName, '#,##0')))

[sunny_talwar]

In the script:

Table:

LOAD *,

  If(frac(Num*10) > 0, Num(Num, '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0'))) as NewNum,

  If(frac(Num*10) > 0, Num(Round(Num, 0.01), '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0'))) as NewNum1;

LOAD * Inline [

Num

35

20.5

40.275

15.25

18.273

];

Output:

[dmohanty]

Hi Sunindia,

Thanks for the quick help.

I am using the expression in a Text Object, using variable (Not in Load Script). Will the above script help?

[sunny_talwar · Accepted solution]

Use the below in a text box object (replace Num with your number or variable)

If you are going to round anything more than 2 decimal point (4.675 -> 4.68 and 4.673 -> 4.67) then use this:

=If(frac(Num*10) > 0, Num(Round(Num, 0.01), '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))

If you are just looking for formatting (4.675 -> 4.67 and 4.673 -> 4.67) then try this:

If(frac(Num*10) > 0, Num(Num, '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))

[qlikviewwizard]

Good one sunindia‌

Nice trick

[dmohanty]

Hi Sunindia,

Thanks again for this.

I have applied the first formula in above post.

However I have some values like 7.5, 35.5.... For these, the result coming as 7.50, 35,50.

Seems the second If Condition is not getting applied properly. Please help!

[qlikviewwizard]

Hi DMohanty

Could you please attach the sample file. It will help the folks to give better solutions.