Group by a field, create a new column for each day and count occurrence

[h12]

Hi guys

I couldn't find a solution to my issue so would be grateful for any input!

Input table:

ID	Stage	Date/Time
1	Level 1	20/02/23 09:16:12
1	Level 2	20/02/23 10:10:23
1	Level 3	21/02/23 04:45:56
2	Level 1	19/02/23 11:43:45
2	Level 2	21/0223 04:45:34
2	Level 3	[null]
3	Level 1	19/02/23 10:34:34
3	Level 2	20/02/23 11:13:43
3	Level 3	20/02/23 11:45:34
3	Level 4	21/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:

Stage	Count on 19/02/23	Count on 20/02/23	Difference yesterday and today	todays date
Level 1	1	1	1	0
Level 2	1	2	1	1
Level 3		1	0	1
Level 4			1	1

UPDATE:

Output desired:

Stage	Count on 19/02/23	Count on 20/02/23	Difference yesterday and today	todays date
Level 1	2	0	0	0
Level 2	0	1	1	1
Level 3	0	1	1	1
Level 4	0	0	1	1

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!

[binuacs]

@h12 One way of doing this#

[Christina_H]

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.

[h12]

@binuacs 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

[h12]

• @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)

[Christina_H · Accepted solution]

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.

[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.

[Christina_H · Accepted solution]

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

[h12]

@Christina_H Thank you!