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

Date difference issue

Viktor1
5 - Atom

Hi all,

 

I'm trying to calculate a difference between two dates. Difference should be expressed in months. I would like to round it to full months (either up or down, I can adjust afterwards).

 

At the moment I am using formula:

 

DateTimeDiff([PD date],[SEDF date],"months")

 

But I am getting outputs with two differences the same as per below:

 

PD dateSEDF dateMonth difference 
2017-07-26 2017-06-011 
2017-07-262017-07-010Issue
2017-07-262017-08-010Issue
2017-07-262017-09-01-1 

 

For some reason 26th Jul - 1st Jul = 0 and 26th Jul - 1st Aug = 0. I need this to show 0 and -1 or 1 and 0.

 

Any ideas? Round and Ceil functions didn't work.

 

Thanks,

 

Viktor

5 REPLIES 5
NicoleJohnson
ACE Emeritus
ACE Emeritus

Can you calculate the difference in days instead of months, and round up/down based on the # of days?

 

Alternatively, if you converted PD date to the 1st of the month (DateTimeTrim formula should work), then you'd be able to compare 2017-07-01 to each of the SEDF dates, which might get you closer to the first option (0 and -1) that you were looking for... but it would change the last row to 2, which I'm not sure is correct either.

 

If either of these seem like options worth pursuing in more detail, let me know if you need some more tweaking!

 

Cheers,

NJ

 

Viktor1
5 - Atom

Thanks Nicole. Using days instead of months and then rounding after dividing by 30 worked great.

 

Viktor

MaverickVZ
7 - Meteor

Thanks!

MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @Viktor1,

 

You might want to consider this solution too:

 

DateTimeDiff(datetimetrim([SEDF date],"Month"),datetimetrim([PD date],"Month"),"Months")

The DateTimeTrim() function will cause the date to become the first of the month at 00:00:00 (a shortcut to the first of the month if you ever need it).  Then the DateTimeDiff() function will easily count the "Months" between the two dates.

 

Keep it simple.  (Simple is Hard podcast reference: Alter-Everything-Podcast/12-Simple-is-hard)

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MaverickVZ
7 - Meteor

Mark,

 

Thanks for this. I was trying to get it to work by getting the month first using Datetimemonth and then using Datetimediff. That didn't work so I used the above solution but needed to add 1 to get the proper result. Both work, I think this is a bit cleaner. One of the things I love about Alteryx is there are many paths to the same solution. I had never used DateTimeTrim so now that's just another arrow in my quiver.

 

Pete

Labels