Using LEFT and INDEX to get around binary issue with Floor()

BenODBS — Tue, 15 Jun 2021 14:41:02 GMT

So myself and a colleague found out quite some time ago that Floor sometimes has issues with certain figures (and Round too). As small as the differences are, this causes problems with Test where they will point out their system test in SQL of the DB doesn't match what Qlik shows. Also sometimes there are conditional colour/symbol trigger issues where it puts a '1' in the 15th decimal place for certain numbers. It is probably binary related too!

My colleague found that someone years ago in QlikView used 'Left' to effectively cut off the figure to the places required to act as a floor in some respect. However because percentage/numbers with required decimal places can vary in number of chars depending on the result it made it tricky to nail down the char number criteria.

So we came up with a possible solution.

left('50.56555334',(index('50.56555334',.))+4) results in 50.5655

index on its own is 3. so 3+4=7 chars. 50.5655 is 7 chars including the '.'

The idea is that it searches for the placement of the '.', counts the number of places then I add the number of chars I want from the '.' depending on how many decimal places I want. Anything to the left of the '.' is included in full.

(This is different when it is a percentage obviously and need to apply number of dec places based on what the figure is before it is in % format, like round/floor.)

I generated over 1 million records and utilised expressions to count/sum or divide by for percentages in KPI charts and bar/line charts. I utilised filters for month/year. The calculations are instant and appear to be correct.

So what I want to ask, am I missing something with this? I wondered about performance with more complex expressions but the data load indexing is meant to be what gives good performance with calculations and filtering? If there is a better way, I'd be interested but I'd like to know at least that what we've done isn't a bad idea.

For reference, this only gives examples with LEFT and INDEX in Text format on Qlik Sense Windows FAQ for may 2021 that I could see so far. I am using Qlik Sense Kubernetes.

Cheers

topic Using LEFT and INDEX to get around binary issue with Floor() in App Development

Using LEFT and INDEX to get around binary issue with Floor()