Consecutive days

Question

Hi,

In the attached data and workflow, can you please let me know how to include the consecutive number of days based on the date of return and date of departure

For e.g the 30 Nov 2024 to 6 Dec 2024 two lines are consecutive. Similarly, 09/12/2024 to 16/12/2024 - 2 lines are consecutives.

Also how do I include another column to show the 1st consecutive, 2nd consecutive.

If you can provide some ideas really appreciated.

Thanks

Regards

Thiviya

Data to test.xlsx

Consecutive days based on location.yxmd

KGT · Answer

OK, my laziness may have provided too many methods here... I was using the formID initially because it was a number available and was unique. The key element is using the Multi-row to tag, the formID has confused it, but it can be anything...

There was a reason why I didn't make the number at the time, but I can't remember.

Back to simple...

Sort the data by [Employee ID] and [Date of Departure] (I used descending as that's what your data was already sorted in).

Multi-row with no group by, called DayGroup and Int32.

IF [Row-1:Employee ID]==[Employee ID] && [Row-1:Date of Departure]==[Date of Return]
THEN [Row-1:DayGroup]
ELSE [Row-1:DayGroup]+1
ENDIF

In that, you can always change the  "[Row-1:Date of Departure]==[Date of Return]" to be "ABS(DateTimeDiff([Row-1:Date of Departure],[Date of Return],'day'))<=2" if you need it to handle a skipped day.

From there, you can group by that DayGroup field.

Thiviya · Answer

Thank you for the detailed information. It is really helpful. I was managed to use that and update the consecutive. But then in the consecutive 2, I have to make sure one day differences also picked up as " consecutive" then how do I ensure the same Form ID as consecutive is included in Consecutive 2? I have sorted the employee ID and Date of Departure in a ascending order. So I am expecting something like this  - In the highlighted blue cells to have the same FORM ID as teh consecutive ? How do I update this in the consecutive 2 formula in the workflow ?

Consecutive days formula workflow.yxmd

KGT · Answer

The RowID labels the first/second of a consecutive group.

The average was my lazy way of identifying each group with a unique (as long as FormID is consecutive-ish) identifier. You can assign the min FormID if you like instead....

In order to do calcs based on the groups, you want to use summarise. So, once each group has an identifier, then you can group by that and combine them, allowing you to do more calcs. These can be joined back on to the table as an additional column.

Take a look at the attached and see if it makes more sense. I removed fields just to make it easier to look at the data... I also added a record to get a 3rd consecutive day. There's a couple of slightly different methods in here, but hopefully it gives you enough to develop something that fits your data.

The thing I will caution looking at that last screenshot, in Alteryx you are dealing with data in a traditional data format rows vs columns. If you are randomly adding totals on some lines and not others, that's more of a report. Do not try and replicate the way things look in excel while processing. A lot of things are set up in Excel in a certain fashion, because that's the only way to do it there and if producing a report, that gets produced at the end.

Consecutive days based on location - KGT edit.yxmd