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

Create interval based on a specific starting date

Motz
5 - Atom

Hi,

 

I'm analysing a bunch of different projects. All projects had deadline 6 months from their starting date. Therefor I need a formula that creates a new field telling whether the date is in the first or the third month of the project plan. 

 

I have attached a sample of the data. But it could look like this:

 

IDStartingYearStartingMonthYearMonthTime
00012017December2017November0
00012017December2017December1,5
00012017December2018January3
00012017December2018February2
00012017December2018March6,2
00012017December2018April7,5
00012017December2018June9
00022017June2017June2
00022017June2017July4,1

 

And I want it to look like this:

 

IDStartingYearStartingMonthYearMonthTimeProjectMonth
00012017December2017November0Null
00012017December2017December1,51
00012017December2018January32
00012017December2018February23
00012017December2018March6,24
00012017December2018April7,55
00012017December2018June96
00022017June2017June21
00022017June2017July4,12

 

Thanks in advance.

3 REPLIES 3
Paulteryx
11 - Bolide

Hi,

 

I've put together a little sample with your data. Essentially you want to merge your month and year columns in the yyyy-mon and yyyy-month format. Then using the DateTime tool we can convert that to a date that we can do math with (2017-01-01,  2018-04-01 etc). Setting up a datetimediff() equation in the formula tool and requiring it return the results in number of months I believe will give you what you're looking for. That said, I noticed in your sample data, you have some starting year/months that before the year and month fields. Therefore in this example you get negative numbers.

ponraj
13 - Pulsar

Samle workflow as below. 

 

workflow.PNG

Motz
5 - Atom

Thank you very much!

Labels