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

Create interval based on a specific starting date

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.

Fireball

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.

Highlighted
Quasar

Samle workflow as below. 

 

workflow.PNG

Atom

Thank you very much!

Labels