How to take the first and last month from a date field and create two new fields/ID

[sheidari]

I have a workflow where I need to grab the earliest and last month for each ID and convert them into two new fields called "Month_Start" and "Month_End". Attached is a sample of the input and output. 

I imagine i would need to use a multi-row formula (min and max?) and cross-tab but not sure how to write the expression.

Any help will be greatly appreciated!

[phoebe_kelley]

Hi! You can use a summarize for most of those, and then a formula tool to calculate the duration. See attached 🙂

[atcodedog05 · Accepted solution]

Hi @sheidari 

approach to your scenario would be to use summarize tool. Grouby Id and find min and max date.

workflow:

Steps:

1. Select Tool: Convert ID to string (not necessary just a safety mesaure)

2. Formula Tool: covert date to Altyer date format

3. Summarize Tool: grouby (ID, fiscal year), min & max of date, sum of revenue

4. Formula Tool: find project length 

Hope this helps 🙂 Feel to ask if you have any questions

[echuong1]

A really easy way to accomplish this is with a Summarize. You're able to calculate the start and end dates with the min and max functions. You can also find total revenue with sum. I used a group by on fiscal year and ID to get multiple lines for different items.

I also used a datetime tool to convert the month values to proper date formats in Alteryx. I then used a formula with a datetimediff() formula to calculate the project length.

Hope this helps!

[sheidari]

Thank you everyone for the quick responses. I got it to work just as expected!