Weekly open case count calculated in a report?

building reports
1

I want to examine open cases metrics and backlog trends, etc. We’ve had this functionality but we moved to a different database and it has not been implemented yet.

Now I can easily set up some logic that checks if a case was open one, two or three weeks ago and calculate the metrics but I’m creating 4 new dimensions and I don’t want to end up creating 52 for a year period or more if I need to examine 5 or 6 quarters.

I’ve tried adding a week-number dimension from an excel-sheet, but I can’t get the logic to work. How would I create an array of case numbers open for any given week?

A case will have a created timestamp and a closed (if applicable) timestamp, and I would count any case opened before or in week X → opened before day 1 of week X+1 and not closed before or in week X.

Thanks!

2

The way I’ve handled this type of thing in the past was to create a table in the database that contains dates - you should be able to do this in Excel if you can’t update the database. The structure is usually something like this:

DATE_VALUE datetime
DATE_YEAR int
DATE_MONTH int
DATE_DAY int
DATE_QUARTER int
WEEK_OF_YEAR int ← usually in the format YYYYWW for easier comparison between years.
MONTH_OF_YEAR int ← usually in the format YYYYMM
QTR_OF_YEAR int ← usually in the format YYYYQ
WEEK_START datetime
WEEK_END datetime
MONTH_START datetime
MONTH_END datetime
QUARTER_START datetime
QUARTER_END datetime
YEAR_START datetime
YEAR_END datetime
HOLIDAY_IND bit
WEEKEND_IND bit
BUS_DAY_SEQ int

BUS_DAY_SEQ is a running field that doesn’t restart at the beginning of a year. It increments for every business day and keeps the value of the previous business day for weekend and holiday dates.
For example, if the earliest date you have is 1/2/2020, which is a Thursday, here’s how the values would look:

DATE_VALUE BUS_DAY_SEQ
1/2/2020 1
1/3/2020 2
1/4/2020 2
1/5/2020 2
1/6/2020 3
1/7/2020 4

1/4 and 1/5 have a BUS_DAY_SEQ of 2 because they are weekend days.

This field make it easy to determine, for example, the number of business days between the create and closed date or between any two dates you’re looking at. In the example above, if you wanted to calculate the business days between 1/2 and 1/7, you would subtract the BUS_DAY_SEQ of 1/2 (1) from the BUS_DAY_SEQ of 1/7 (4) and add 1 → 4 - 1 + 1 = 4.

In the SQL for your report, you would join from the created timestamp to the DATE_VALUE in the DATES table with an alias something like StartDates. You would also join from something like IsNull(closed timestamp, Current Date) to DATES with an alias something like ClosedDates. If you’re filtering the report on a date range, you would filter both StartDates and ClosedDates on DATE_VALUE between start of the date range and end of the date range. You would then filter your data on created timestamp <= end of the date range and closed timestamp >= start of the date range.

To determine whether a record is active in any given week, you would look at whether the week number is between the week of the created date and IsNull(closed date, Current Date)

The other fields in the DATES table give you options for looking at things by year, quarter, or month instead of/in addition to by week.

-Dell

1 Like
3

You could also create your calendar as a free-hand sql common table expression query as an alternative to Excel. Here is an example using SQL Server that you can modify as you see fit.

1 Like