Calculating Historical Backlog Data

[eah1]

Hi!

I have a pretty tricky question I have wrapped my head around for two days without a solution. Any tips for me?

Basically, I have (several hundred k rows) case handling data in the following format.

CASE_CREATION_DATE	CASE_COMPLETED_DATE	__CASE_DUE_DATE	Category
2020-01-10 09:10:11	2020-01-13 23:00:16	2020-01-12 09:10:11	Team A
2020-10-23 23:00:16	2020-01-24 14:50:09	2020-10-25 23:00:16	Team B
2021-08-30 15:44:54	2021-08-31 11:23:34	2021-09-01 15:44:54	Team B
2021-12-04 11:23:34	2021-12-07 15:44:54	2021-12-06 11:23:34	Team A

What I would like to do with this data is create a historical backlog list starting from the start of year 2020 until today. It would show the number of cases that were uncompleted at the given time, like this table:

BACKLOG_TIME	CATEGORY	BACKLOG_TYPE	COUNT
2020-01-01 00:00:00	Team A	INCOMPLETE	43
2020-01-01 00:00:00	Team A	INCOMPLETE_ PAST_DUE_DATE	12
2020-01-01 00:00:00	Team B	INCOMPLETE	5
2020-01-01 00:00:00	Team B	INCOMPLETE_ PAST_DUE_DATE	3
2020-01-01 04:00:00	Team A	INCOMPLETE	33
2020-01-01 04:00:00	Team A	INCOMPLETE_ PAST_DUE_DATE	23
2020-01-01 04:00:00	Team B	INCOMPLETE	1
2020-01-01 04:00:00	Team B	INCOMPLETE_ PAST_DUE_DATE	64
etc. until 2021-06-02 12:00:00

Basically, the second table is a list of times ranging from the beginning of 2020 to today (every hour ideally, but this might be too large of a data sample, every 4 hours or 6 hours also fine), which are then subdivided into the different Teams (there are more than 2 actually), and then further into...

per team INCOMPLETE counts [backlog_time > case_creation_time AND backlog_time < case_completed_time AND backlog_time < case_due_date]

and

INCOMPLETE_ PAST_DUE_DATE counts [backlog_time > case_creation_time AND backlog_time < case_completed_time AND backlog_time > case_due_date]

How on earth can I automate this?

Cheers in advance to anyone who can figure this out!

[Luke_C]

Hi @eah1 

I think this would be achievable, but time functions can be a bit tricky. Do you have a larger set of sample data you could post? I think that would be helpful for me or anyone else to take a pass at. 

[eah1]

Hey,

Awesome to hear! Here's a 30k row data sample if you want to take a stab at it.

Thanks!

[Luke_C · Accepted solution]

Hi @eah1 

Here's my attempt at this:

1. Added record ID for each case (this helps me visually differentiate the rows while working through the rest of the workflow)
2. Generate additional rows for every 4 hours the case is open
   1. I start with the the case creation date at 00:00:00 and add four hours until it exceeds the case completed date.
3. Use formulas to identify when cases are incomplete vs incomplete & past the due date
4. Summarize the data
5. Filter out records where both values are 0 (you may choose to remove this)
6. Transpose & rename to your format

[eah1]

Wow! Such an elegant solution, thanks a bunch 🙂 I tried fiddling with much more complicated structures that never worked out.