For every record count number of records in a dataframe within a time interval

[Thomasstrand]

I need to count the number of observations (records) that are within every single observation`s "Started" and "Completed" date in Alteryx. With other words, I want a new field that indicates how many workflows that are running simultaneously as a particular record or workflow. Do anyone have an advice how to do this? In the following I explain how the logic or conditional function needs to be:

Let`s take the yellow record as an example, there are two possibilities to be within the date range :

1. Records are within the yellow record`s date range if they have earlier started date but a completed date later than the yellow record`s stated date. 

2. Records are within the yellow record`s date range if they have later stated date than the yellow record`s start date BUT their started date is earlier than the yellow observation`s completed date. 

In conclusion, I need a workflow that create a new field counting number of workflows that runs within a particular workflow`s run interval. When programming I would typically solved this with a "for-loop" and "conditional statements", but I am not sure how to implement it in Alteryx. I were trying to implement the multi-row formula, but without any success, any advice?  

[atcodedog05]

Hi @Thomasstrand 

Can you provide the input and expected output in excel file for this?

[RolandSchubert]

Hi @Thomasstrand ,

if I got you right, it's basically a comparison of date/time. 

What do you think about this approach:

1. Convert started and completed field to datetime datatype

2. Create a "cartesian product" of workflows (combine all workflows in the list with all workflows in the list)  - you could also restrict to a single workflow on the "left" side

3. Remove "self-cominations (workflow 1 with workflow 1)

4. Filter by datetime range according to condition

Do you think it could work for you?

Best,

Roland

[Thomasstrand]

I have now attached both the input and expected output file. 

[RolandSchubert · Accepted solution]

A few modifications needed, but in general, my approach seems to work ...

 

[atcodedog05]

Mine would be similar 😅 @RolandSchubert has pretty much nailed it 😀

[Thomasstrand]

This solution was spot on Roland, thank you!