Filter three consecutive days

Question

Hi Everyone,

I would like to do a filter:

True: this id has more than 5 orders in any three consecutive days.

I can't think of how to check three consecutive days? Can anyone help me? Below is some example.

Order TimeOrder ID2018-01-13 17:14:2612018-01-14 18:52:4712018-01-15 19:47:1012018-01-15 22:18:0112018-01-16 12:42:0112018-01-17 15:48:3812018-01-17 19:09:4012018-01-13 17:14:2622018-01-14 18:52:4722018-01-15 19:47:1022018-01-15 22:18:0122018-01-15 12:42:0122018-01-17 15:48:3822018-01-17 19:09:402

Please notice that althogh Order ID 2 has 5 orders in 0115 and 0117, It will be false because no order in 0106. That is not three consecutive days. Acutally I already filtered the 0 order date. Just a reminder.

So what I want for the output would be:

Order TimeOrder ID2018-01-15 19:47:1012018-01-15 22:18:0112018-01-16 12:42:0112018-01-17 15:48:3812018-01-17 19:09:4012018-01-13 17:14:2622018-01-14 18:52:4722018-01-15 19:47:1022018-01-15 22:18:0122018-01-15 12:42:012

CharlieS · Answer

I agree with @Qiu that there seems to be a bit of discrepancy between your described logic and provided output.

To produce your output I interpreted your logic as: only take the LAST 3 sequential days of records. Following this I am able to produce your desired output and have attached the workflow.

Check it out and let me know if you have any questions on how this works.

20210301-3DaySequence.yxmd

luo · Answer

@Qiu Sorry for the misunderstanding. total amount of orders of 0113, 0114, 0115 is not equal or more than 5. So it will not be considered as true.

Qiu · Answer

@luo

For ID 1, we should take the newest 3 consecutive days, since it is consecutive from 0113 to 0117.