DateTimeAdd function giving inconsistent results

[sagar_ghimire]

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.

[alexnajm]

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

[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 · Accepted solution]

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/b...

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

[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.