community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

DateTimeDiff

Asteroid

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

Alteryx Certified Partner

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

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.

 

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Alteryx Certified Partner

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

 

 

Asteroid

Thanks @LordNeilLord

Asteroid

Thanks @MarqueeCrew

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

Labels