Create End Date from Start Date on next row

[Lou_Alicea]

I have a spreadsheet with 3 columns as seen below (Name, Position and Position Start Date)… and would like to use Alteryx to create a 4th column (Position End Date) using a formula… something like… “Position End Date = Position Start Date from next row minus 1”

In SQL I’ve used a Row Over Partition… but I wonder if this could be done in Alteryx especially if the data source is an Excel Spreadsheet.

For the final row in the data set (Current Position) a set time in the future would be inserted ie… 12/31/2099

Name	Position	Position Start Date
John Doe	Rep	4/5/2015
John Doe	Sr Rep	6/21/2016
John Doe	Supervisor	2/11/2018
John Doe	Manager	3/17/2019

The final output would have 4 columns (see below):

Name	Position	Position Start Date	Position End Date
John Doe	Rep	4/5/2015	6/20/2016
John Doe	Sr Rep	6/21/2016	2/10/2018
John Doe	Supervisor	2/11/2018	3/16/2019
John Doe	Manager	3/17/2019	12/31/2099

[carlosteixeira · Accepted solution]

Hi @Lou_Alicea .

to do this you can use the tool Multi Row - Formula.

Look at this workflow.

Holp this help you.

Best Regards

[Thableaus · Accepted solution]

Hi @Lou_Alicea 

Here's a possible solution:

- I used Multi-Row Formula Tool and applied a condition saying if it doesn't find a next date, it should assume "12/31/2099".

- Also don't forget to group by Name

WF attached.

Cheers,

[Lou_Alicea]

It works!!!  Awesome... You ROCK!!!