How to check if the date in the current row is 1 day after the previous row?

[danielstefanng]

Hi,

I need to get Alteryx (I am assuming the Multi RowFormula tool) to fill out the 'Is the current date a day after the previous date? (Yes/No)' field. The values in the chart belowis what I would expect it to be.

Basically, I need the check if the 'Date' in the current row is 1 day after the previous row, and then populate the mentioned column with 'Yes' or 'No' accordingly.

I would also need to group this by Name. Please note I have already sorted my data by Name, and then by Date.

Name	Date	Is the current date a day after the previous date? (Yes/No)	Comment
James	23/07/2018	No	This is a No because it is the first date for that name, therefore there is no previous date.
James	3/09/2018	No	No, because the date in this row is not a day after the date in the row above.
James	6/09/2018	No	No, because the date in this row is not a day after the date in the row above.
James	22/10/2018	No	No, because the date in this row is not a day after the date in the row above.
James	23/10/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
James	24/10/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
James	25/10/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
Anne	23/07/2018	No	This is a No because it is the first date for that name, therefore there is no previous date.
Anne	24/07/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
Anne	25/07/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
Anne	26/07/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
Anne	30/07/2018	No	No, because the date in this row is not a day after the date in the row above.
Anne	31/07/2018	Yes	Yes, because the date in this row is a day after the date in the row above.
Anne	31/07/2018	No	No, because this line has the same date as the line above.

Thanks for your assistance.

Kind regards,

Daniel

[DavidP · Accepted solution]

Hi Daniel,

Yes you can do it with Multi Row formula, but in order to do date calculations, you just have to convert your date to the Alteryx date format, like this. Also see workflow attached.

[danielstefanng]

Thanks David,

However, I am getting the attached errors . Apologies, should've mentioned that my current dates are in this format:

YYYY-MM-DD and as per your instructions, should be converted to DD/MM/YYYY. Thanks for your assistance.

Kind regards,

Daniel

[danielstefanng]

No worries, all good. I didn't have to convert the date because Alteryx recognises the YYYY-MM-DD format, so the formula below worked.

if datetimediff([Date],[Row-1:Date],'days')=1 then 'Yes' else 'No' endif

Thanks again,

Daniel

[Hakimipous]

@danielstefanng please make sure to mark that post as solved if this is the case :)

[DavidP]

Yes, yyyy-mm-dd is actually the Alteryx date format. The reason I used datetimeparse in my first post was because the dates in your original post was shown as dd/mm/yyyy. In this case, the date has to be converted to the Alteryx format.

Anyway, glad you got it working.