Hi guys I couldn't find a solution to my issue so would be grateful for any input! Input table: IDStageDate/Time1Level 120/02/23 09:16:121Level 220/02/23 10:10:231Level 321/02/23 04:45:562Level 119/02/23 11:43:452Level 221/0223 04:45:342Level 3[null]3Level 119/02/23 10:34:343Level 220/02/23 11:13:433Level 320/02/23 11:45:343Level 421/02/23 10:34:34 Everyday IDs will be added to the table, stages will be added to the table and a timestamp will be added to when it reachs that stage. The [null] means its not reached that stage yet but can do so toady/tomorrow etc. (theres a max of say 7 levels) Output desired: StageCount on 19/02/23Count on 20/02/23Difference yesterday and todaytodays date Level 11110Level 21211Level 3 101Level 4 11 UPDATE: Output desired: StageCount on 19/02/23Count on 20/02/23Difference yesterday and todaytodays date Level 12000Level 20111Level 30111Level 40011 The slight update is that to avoid duplication counting of ID, we would find the max date/time for that ID. Eg. ID 1 hmoved two levels on the 20th, therefore I would only count it where its the maximum date/time (i.e level 2) and not level 1. Many thanks in advance!

Hi @h12

Updated workflow attached, which will count only the last level change in a single day for each ID, and add all date columns. To simplify, I've labelled columns as Today and Yesterday for the formula, if you need Yesterday to be labelled with the date you could use another dynamic rename afterwards.

I had to make up some additional data for today. This comes back to my previous question - will there always be data for Today and Yesterday? If not, the formula will fail. E.g. if there's no data from the weekend, it won't work correctly on Mondays.

In that case, here's a final update. It compares the latest two days from the dataset regardless of what they are.

All comments

binu_acs

@h12 One way of doing this#

Multi_Row_210223.yxmd

Will the data always contain all three dates? This should work if it does. If not, you'll need to adjust the first filter to include e.g. the latest 3 dates.

@binu_acs Thank you for this. Sorry, i've realised that my request didnt include grouping by ID. I have updated it now. My previous request would have counted multiple dates for a particular ID, but in order to count the ID once, i proposed that where the ID moves two or more levels in the same day, only the maximum level is counted between the two. Also, do you know a way i could automate the difference in date? I noticed on the second formula tool I would need to update the 'count on 21_02_23' every day

@Christina_H Thank you for this - yep the dates will remain in the data (its like a timestamp) and as more and more IDs get worked on additional dates will flow through.
Sorry, i've realised that my request didnt include grouping by ID. I have updated it now. My previous request would have counted multiple dates for a particular ID, but in order to count the ID once, i proposed that where the ID moves two or more levels in the same day, only the maximum level is counted between the two.
I think I would do this right at the start of your flow, but then I am not sure how the date parsing would work.
Also, the first filter tool looks at only the last two days, which is correct, but I was hoping that the 19th would also show in the output as a column if thats possible (and then as more dates flow through the columns continue to grow and keep a record of the counts)

Hi @h12

@Christina_H Thank you soo much for this - sorry for the delayed reply, have been trying to figure my ask out the entire day! Agreed, weekends wont count so Monday's yesterday will actually be Friday.

In that case, here's a final update. It compares the latest two days from the dataset regardless of what they are.