DateTimeAdd function giving inconsistent results

Why are we getting different results between DateTimeAdd([Field1], -(7/5)*45 , "Days") and DateTimeAdd([Field1], -(45*7/5) , "Days")

When we have Field1 = "2025-03-16" for instance, we get 2025-01-13 and 2025-01-12

What are we overlooking? Both -(45*7/5) and -(7/5)*45 should result in -63.

Thanks in advance.

Data Investigation

Accepted answers

Pilsner

Hello @sagar_ghimire,

This is a great spot, I agree with you that this seems like it should give the same answer; however, after some digging around, I have discovered the following:

First of all, if you want the answer to be standardised, you can add brackets in to ensure that the calculations (the multiplication and division) are performed in the same order, within each expression: (note I do not recommend this, as explained below)

This makes sure that each time you do 7/5 before you then multiply this by 45.

Next, and perhaps more importantly, why is this happening? Well, I believe it could be linked to the way to formula tool handles numbers under the hood. @Gaurav_Dhama_ wrote a great article explaining more on this here:
https://community.alteryx.com/t5/Engine-Works/Handling-Unusually-Large-Numbers-in-Alteryx-Designer/ba-p/1407366

But to summarise the most relevant parts in your case:
- Under the hood the formula tool converts everything into a double.
- Double values are actually stored as Base 16 encoded values, and when decimals are involved this means it sometimes has to round the value. In this case, it ends up setting 7/5 equal to 1.399999.... as it cannot exactly represent 1.4 in Base 16.

With this in mind, if we go back to your initial calculations we get the following:
1) -(7/5)*45 = -1.399999 * 45 = -62.999999
2) -(45*7/5) = -(315/5) = -63

Interestingly enough, the two answers you provided are 62 and 63 days away from your input.

The next part is where I have made a pure assumption so please bare that in mind. However, as you are getting two different dates, one -62 and one -63 days away from the input, it appears as if the date time add function is actually taking the integer component only, therefore -62.999 becomes -62.

This hypothesis is further backed up by the following where you can see the values returned appear to both be 63, but under the hood must actually be slightly different:

Finally, my actual recommendation. In order to standardise the results, to the correct value, 63 days ago, you may want to include the Floor function inside your DateTimeAdd function. This makes sure that this value is rounded to the same integer for both -(45*7/5) and -(7/5)*45

I have attached the workflow I used below although full credit to @alexnajm as I initially downloaded the file provided.

I hope this all makes sense, I appreciate its quite fiddly, as mathematically the formulars are the same. If you have any further questions please feel free to ask.

Regards - Pilsner

DateTimeAdd function giving inconsistent results.yxmd

All comments

alexnajm

That is so curious - I am unsure after my testing. Posting my workflow in case it helps anyone else

DateTimeAdd function giving inconsistent results.yxmd

abacon

@sagar_ghimire I ran tests as well and got differences, very curious. Good call out.

Bacon

jrlindem

The best I could find in researching was that Alteryx maybe treating the 7/5 as a float and therefore not treating it in the mathematical sense that we're used to seeing. If there is any validity to this, it could be that it won't "tick" forward the date unless a whole day is realized. So if Alteryx, for whatever the reason, is treating this as less than the literal value, it could be shorting by a day.... This is more a theory than anything i've proven out. I also experimented with a workflow and experienced the same.

Obviously the recommendation to resolve is to use the derivative of the formula that is netting the expected result, but as far as the why; you got me!

-Jay

Pilsner

Hello @sagar_ghimire,

This is a great spot, I agree with you that this seems like it should give the same answer; however, after some digging around, I have discovered the following:

DateTimeAdd function giving inconsistent results.yxmd

Pilsner

@jrlindem, I've just seen your reply (I was mid typing myself 😄), but your understanding seems to be similar to mine, so hopefully we're onto something here. My explanation might not be perfect, but I know encoding decimals can cause some issues, so it's likely the cause of the discrepancy here.

KGT

I agree with all that @Pilsner, and I'll add one more thing.

The DateTimeAdd formula accepts a double/float as the middle parameter, however will only ever add the integer portion. So, when the term ends up being 62.99999... then the integer portion, not the rounded integer, will be taken. This is because the formula works on complete days (in this case). It's totally in line with how I would expect the formula to work, however without the storing doubles issue that causes the non-integer. Definitely something worth raising.

sagar_ghimire

Thanks, can you point me to more documentation on this part?

"Double values are actually stored as Base 16 encoded values, and when decimals are involved this means it sometimes has to round the value. In this case, it ends up setting 7/5 equal to 1.399999.... as it cannot exactly represent 1.4 in Base 16."

Gaurav_Dhama_

The idea lies in the the way calculation happen in a programming language.

Every programming language converts everything into binary. Now that we know this, lets dig deeper into what is happening.

45*7 is 315 which can be converted to binary easily.

then divide by 5 (which can also be a binary) thus you get exact result 63.

However, 7/5 is 1.4, which cannot be defined in an exact binary. It results in a repetiting binary number, which when you convert comes close to 1.39990 something.

Which when divided by 5 will give you 62.995.

Hence you must be seeing the difference.

Now that is a very minute difference, so why we are getting difference in dates, that is because of time probably (I can test this and confirm), because dates alone just mean date + mind-night time. So now you are adding only 62 days and some time. On the other hand in another calculation you are adding 63.

Thus the difference.

KGT

Also check out IEEE 754 for more information. Basically it comes down to the way data is converted to binary to be stored. If you're programming in COBOL, not much of an issue but post mid-80's it's been a factor.

In this case, it may be something where Alteryx needs to perform the calc and round, but that would break the use of the tool where the return is just full days as it may round up, which we wouldn't want. This is essentially an odd case where a couple of things come together to produce a bad result. The solution would be to only ever provide an integer for that parameter.

Pilsner

@KGT , that's a great explanation about the full days, thanks for explaining that.

@sagar_ghimire , regarding documentation, if you want to know about the data types themselves, then you can see Alteryx's documentation here
https://help.alteryx.com/current/en/designer/file-types-support/data-types.html##

However, if you want to understand how doubles etc, work under the hood, in terms of being encoded, then this isn't specific to Alteryx. I'm not aware of an Alteryx article explaining this, but I have found one elsewhere:
https://medium.com/@JeffreyLean/the-secret-life-of-floating-point-types-in-programming-languages-e25bc55d6123
If you scroll down approximately halfway in this article, they explain the way computers store decimals in more detail by using the example calculation 0.1 + 0.2 = 0.3000...04.

dreldrel

The discrepancy happens might because of the order of operations combined with integer division and implicit casting inside Alteryx. 7/5 is not exactly 1.4 in this context, it is treated differently, which leads to truncation

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703