Consecutive days

[Thiviya]

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

[KGT]

These types of processes normally change a bit depending on the greater goal, but once you get it "working", you can start playing with it.

This formula should help you identify which rows are consecutive, however you may choose to break the 2 conditions into 2 separate formulas. I haven't included any of your grouping in this though...

Create a field called "Consecutive"

IF [Row-1:Date of Departure]==[Date of Return]
THEN Average([FormID],[Row-1:FormID])
ELSEIF [Row+1:Date of Return] == [Date of Departure]
THEN Average([FormID],[Row+1:FormID])
ELSE 0
ENDIF

To label the rows, use another multi-row to create a field called RowID:

IF Consecutive = 0
THEN 0
ELSE [Row-1:RowID]+1
ENDIF

[Thiviya]

Thanks for the prompt response. What is the purpose of "Average" on the formula.  The result should be still show  number of days"   in the consecutive column  or have word " consecutive" , then another column with "total Trip duration".  Then I can do formula whether it is less than 21 days or more than 21 days etc...

Also what is the purpose of the ROWID?  

Thanks

[Thiviya]

I think I have worked out the "consecutive" and number of days. What formula to use to to get the sum as shown below for the same location and consecutive . IT should not be added as 9+8+8 = 25.. for the same location. 

So how do  I separate the 1st consecutive, 2nd consecutive ? That way when I sum up I can sum up by 1st consecutive, 2nd consecutive? 

[KGT]

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.

[Thiviya]

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 ?

[KGT]

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.