number of decimals used in floor() function

[curiousfellow]

Why is this  floor(180.00000000000000-0.00000000000001,30)   180 as result and this

floor(180.00000000000000-0.0000000001,30) 150 as result.

I suppose it has to do something with the nuber of decimals used for this function.

I want floor(180.00000000000000-0.00000000000001,30)  to give 150 as result but 

floor(180.00000000000001-0.00000000000001,30) 180 as result and don't know how to achieve that when the number of decimals used is limited.

[marcus_sommer · Accepted solution]

It's related to the used number-system which is a binary system and supports only a limited number of digits. More explanation could you find here:

Rounding Errors - Qlik Community - 1468808

- Marcus

[MarcoWedel]

[Anonymous]

QlikView will only see the first 13 decimal digits, and your example has 14 hence they are equal as Marco pointed out.

Curious, do you actually have data with that many decimal digits, or ae you just testing QlikView?

I once sent a report out showing a unit was transmitting say 30.255 dBm. I was laughed at. I'm not laughing at you, just curious as to why.

[curiousfellow]

I really have that many digits 🙂 , I don't present but use them to calculate in what class they get.

150- <=180

The class function of Qlik uses 150 <= - 180, so 180 gets the wrong class.

I now cut the data less digits. Result is that 180.00000000000001 appears in the wrong class but I hope that is acceptable.

[marcus_sommer]

With some extra efforts you may be able to extend the usable range of digits within calculation. One thought goes to split the numbers into their integer- and their float-part. In this case maybe to something like:

(floor(Number1) - floor(number)) / 30 + (30 * sign(frac(Number1) - frac(Number2)))

Another approach may be to use a macro-function for this calculation. Never checked how many digits VBS supports but AFAIK it used windows libraries and not the Qlik number system. Within the script such function could be easily used - within the UI it would need a not recommended adjustment to the easter egg settings to enable this feature globally.

Similar things might be working with other external tools like R which could be also integrated. Probably there are even more possibilities but not each one would be worth to be implemented.

- Marcus