We will just misrepresent the data in a plausible way

[plonq]

I have run into a puzzling issue with a report that I recently migrated to one of our newer systems. While most of the problems in migrations of this kind tend to be with the source data (somebody accidentally drops a table during the move), the problem this time is slightly more rounded.

Rather, the problem is with rounding.

In my mind, the main cause of the problem is that our executives don't like to see decimal places. Too messy. Ain't nobody got time for that. Don't sweat the small stuff. Give us nice, woody, whole numbers to deal with. Of course, in the world of data and applications, the fractions are an important part of the equation.

The result is that our old system reported that 20 - 44 = -24. Our new system reports it as -25. The thing is, the new system is actually correct, but hey - that's what you get for ignoring the decimal places. It's just that when one of our pointy-hairs higher up in the company see something like 20 - 44 = -25, they scratch their pointy-haired heads, scrunch up their little faces in confusion, then start heating up the cauldrons of tar.

I inherited the equations in this job, and I have tried a number of approaches, but no matter how much I play with CEILING(), FLOOR(), TRUNCATE(), ROUND(), FUDGE() or the like, it seems to insist on doing the maths before it does the rounding.

I will give you some more specific numbers from the report itself.

This year's bullshit metric is 19.73%, which the report rounds to 20%
Last year's bullshiit metric is 44.44%, which the report rounds to 44%

In the old system, it seems to do the rounding first, so when we show the difference between this year's bullshit and last year's bullshit, we see that we are 20% - 44% = -24% off from last year's bullshit.

HOWEVER.

In the new system, it does the calculation before it does the rounding, so even though it properly displays 20% and 44% respectively, regardless of my petitions for it to do otherwise, it calculates 19.73% - 44.44% = -24.71%. Guess what it rounds that to.

If I could just apply a blanket CEILING to negative values, and FLOOR to positive values then I could resolves this issue in an afternoon, but there are very specific failings that need to pile up properly for this to arise. Ultimately I need to employ some kind of Jedi mind trick to convince this software that it needs to forget that the decimal places exist after it rounds the numbers in its terms. I wonder if it has a program setting for "make it logical, not right".

Comments

[pierrekrahn.livejournal.com]

On really old land titles (circa turn of the previous century) measurement tools literally varied depending on the temperature. The surveyors used a physical chain with, obviously, links.

Each link was approximately 7.92 inches. 100 links would equal approximately 66 feet... a number that appears quite frequently in new titles because we convert links and chains to feet when old properties are transferred. Most (over 90%) of road allowances to this day are either 66 feet or 99 feet for that reason.

Now I say "approximately" on those measurements because the chain would expand or contract by a few inches depending on the season. These 100+ year old titles do actually say "6 chains and 10 links more or less". I'm not kidding. They contain the words "more or less"! You have no idea how much of a legal headache this causes in today's day and age.

Anyway, I think you should put "more or less" next to each figure in your report. There are legal precedences of using it thusly.

[fuzzytoedcollie.livejournal.com]

Does this happen if you create a local temporary integer, copy the float into the temporary integer, process the maths as integers, then convert the final result back into a float?

Or, maybe there is a #pragma or #clue or something you can give the compiler/interpreter to disrupt its normal behavior? Maybe an optimization is optimally messing things up here?

You're probably way ahead of me, but I thought I'd try anyway.

Good Luck!

[plonq.livejournal.com]

I eventually found that by multiplying each side of the equation by 100 and then truncating, I could force it to do the maths wrong. I suppose it could be considered a virtue of the software that it took that much effort to make it behave badly.