Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DateTimeDiff

mb1824
9 - Comet

Hi,


As per below screenshots, I have [Period] & [Max_Period], which both have type date, and I have created [Check] with the formula DateTimeDiff([Max_Period],[Period],"months"). I was expecting the results of Check to be 3 (for record 1), 2 (for record 2), 1, 0 however I have got 2, 2, 0, 0

 

What am I doing wrong?

 

Alteryx_date diff 1.JPGAlteryx_date diff 2.JPG

8 REPLIES 8
LordNeilLord
15 - Aurora

Hey @mb1824

 

That is a strange one, it must be something technical that Alteryx does but I can't find any documentation about it. @MarqueeCrew do you know why Alteryx does this?

 

I have found a work around for you which is....

 

DateTimeDiff(DateTimeTrim([Max_Period],"months"),DateTimeTrim([Period],"months"),"months")

DateTimeDiff2.PNG

Eliot_with_cat
7 - Meteor

According to my understanding, this unit of this formula is taking month. In case the different is in decimal place, maybe Alteryx will take the nearest integer to display.

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@mb1824,

 

As @LordNeilLord tagged me I will give you my take on this.  The DateTimeDiff() calculation is literally counting the whole months between the occurrence of a date in the two month values.  It is not counting the logical view of calendar months.  If you were looking at the first of the month to the first of the month (what your trim function is doing) then the count from January to April will be 3.  Though I didn't describe this in Marquee-Crew-s-Guide-to-Dates, it doesn't surprise me that the calculation is based upon the day of the month.  Suppose you were counting months from July 31 to August 30th.  Would you consider that a month?  It takes a whole lot more programming energy to calculate the count of months based upon the date sitting on the end of the month.

 

I would accept @LordNeilLord's solution and would consider saving the expression for future use.  If you use this formula often, you might consider this as a learning opportunity to create a new macro.  

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
LordNeilLord
15 - Aurora

Thanks for your insight @MarqueeCrew

 

This is different behaviour to other systems I'm used to when DateDiff on months will just consider the month part of the date

 

 

mb1824
9 - Comet

Thanks @LordNeilLord

mb1824
9 - Comet

Thanks @MarqueeCrew

mckeyes
5 - Atom

@MarqueeCrew wrote:

@mb1824,

 

Suppose you were counting months from July 31 to August 30th.  Would you consider that a month? 


If I am counting by month, then yes, I would consider that one month. That's the behavior on every other platform.

iludeiro
5 - Atom

I had a similar situation as the one described above and I was wondering why the month # calculation was not giving me the results I anticipated. Thank you so much for providing this solution!

Labels