Count Daily Workload / Open Tickets Per Date

[intapiuser]

Overview:

Many times, we'd like to analyze workload over time.
The following article describes a modeling solution approach to an often asked question: "How many active items are there per day?"

Analytical Need:

Counting the number of open tickets per date.

Modeling Challange:

• Many to Many relationships - There can be multiple tickets per date, and there are multiple dates in which the ticket was open (all dates between Start Date & Resolved Date)
• Handling tickets that are still open - Tickets with resolved date set as Null.

Solution:

LOGIC:

• New field for Resolved Date:
  Creating a new field for temporary Resolved date, that will assign today's date in case the Resolved Date is Null.
  The data will be updated daily, so this field will be populated daily.
• Relationship table:
  Creating a relationship table connecting the Date Dimension and the Tickets tables.
  Dates will be flattened. The relationship table will hold a record per each date in between the Start & Resolved dates of each ticket.

SOLUTION STEPS:

WE'LL USE THE BELOW EXAMPLE TO DEMONSTRATE THE SOLUTION APPROACH:

Step 1

Create a new field for a temporary Resolved date. For the purpose of the example, let's say today's date is 7-31 -2017. The updated Tickets table will look like this:

SQL Syntax:

CASE
 WHEN [Resolved Date] IS NULL 
     THEN createdate(getyear(now()),getmonth(now()),getday(now()))
 ELSE [Resolved Date] 
END

• Name the field 'New Resolved Date'
• Set the field to be of type Date-Time
• Set the original Resolved Date field as invisible.

Step 2

Create a relationship table connecting the Date Dimension and the Tickets tables.

SQL Syntax:

SELECT distinct   d.Date,

   t.StartDate,

   t.[New Resolved Date]
   

FROM

   Tickets t

JOIN

   DimDate d

   ON t.[Start Date] <= d.Date

   AND d.Date <= t.[New Resolved Date]

• Connect the relationship table to the Date Dimension on the Date field.
• Connect the relationship table to the Tickets table both on Start Date and New Resolved Date fields.

DASHBOARD:

Choose the date from the date dimension, and count the number of IDs per date.

• Note: If the dates in your Tickets table contain exact timestamps (rather than being converted to 12:00:00 AM), you should convert them to 12:00:00 AM of the relevant date to enable the join with the Dates dimension. This can be done using the following common syntax: 

createdate(getyear([Date]),getmonth([Date]),getday([Date]))

Relevant ecdata link