WorkoutWednesday 2024-05 – Case SLAs

This is my intended solution for the week 5 challenge of #WorkoutWednesday.

Concept

As mentioned in the challenge, there is no straightforward DATEADD() function in SAQL. However we can convert any date into a unix timestamp, which is the number of seconds since 1st of January 1970 (read details here). Once that is done, we can add any amount of seconds (3600 for an hour, 86400 for a day, etc.) and convert it back to a date.

In addition we need to figure out a way to start the SLA counter at midnight after the case was opened. For this we would need to find the unix timestamp for this point in time. And we need to calculate the number of hours between the open and close date. There is a “daysBetween()” function, but it obviously only returns the days. And once we did the previous steps, this number is just the difference of the unix timestamps we would have created.

Solution

After loading the data into CRMA and creating a dashboard, we can add a chart and change the view to the SAQL editor.

Here is the full code I used, the syntax highlighting makes it easier to read. Below I provide a breakdown of the code.

Start the SAQL query by loading the dataset and grouping by the required dimensions:

q = load "cases2";
q = group q by ('ID', 'Open_Date', 'Close_Date', 'Case_Title');
q = foreach q generate q.'ID' as 'ID',
                       q.'Open_Date' as 'Open_Date',
                       q.'Close_Date' as 'Close_Date',
                       q.'Case_Title' as 'Case Title',

From innermost function to the outermost:

1. convert the open date string to an actual date
2. convert the date to a unix time stamp (epoch time)
3. Divide it by 86400 (the number of seconds in a day)
4. Round the number up to get the first second of the next day (ie midnight)
5. multiply by 86400 to go back to the unix timestamp

ceil(date_to_epoch(toDate(q.'Open_Date', "yyyy-MM-dd HH:mm:ss"))/86400) * 86400 as 'Epoch SLA Start',             

Do the same as before but calculate the number of seconds for the SLA by multiplying the SLA in days with 86400. If you remember high school math’s, you could combine the two terms to make it shorter; I chose to do it this way because it seems clearer to me what is actually happening.

ceil(date_to_epoch(toDate(q.'Open_Date', "yyyy-MM-dd HH:mm:ss"))/86400) * 86400 + avg(q.'SLA') * 86400 as 'Epoch SLA Close',

Finish the first query by calculating the unix times for the open and close dates and start a second query to calculate the measures we actually want.

                       date_to_epoch(toDate(q.'Close_Date', "yyyy-MM-dd HH:mm:ss")) as 'Epoch Close Date',
                       date_to_epoch(toDate(q.'Open_Date', "yyyy-MM-dd HH:mm:ss")) as 'Epoch Open Date',
                       avg(q.'SLA') as 'SLA';
q = foreach q generate q.'ID' as 'ID',
                       q.'Open_Date' as 'Open_Date',
                       q.'Close_Date' as 'Close_Date',
                       q.'Case Title' as 'Case Title',

Calculate the SLA Status with a simple case statement, comparing the actual close date with the maximum allowed close date to be within SLA. and calculate the difference between close date and open date and divide by 3600 to get the total number of hours the case was open (may be greater than the SLA, since we count the absolute number of hours from first opening the case to closing it)

                       case 
                       when 'Epoch Close Date' <= 'Epoch SLA Close' then "SLA Met"
                       else "SLA not met" end as 'SLA Status',
                       (q.'Epoch Close Date' - q.'Epoch Open Date') / 3600 as 'Hours open';
q = limit q 2000;

Different Approaches

Roman Michalik pointed me to the fact that CRMA automatically creates the date parts of any given date (open_date_day, open_date_month, etc.), which I was aware of. What I didn’t know was that it also creates “Open_Date_sec_epoch” which means I can just get rid of all the date_to_epoch() functions in my code.

Ada Xu, did a great write up of her thought process and subsequent solution. Her approach to finding midnight of the following day to start the SLA is quite different from mine and shows that there are often many ways to achieve something in code.

Let me know if this makes sense, if you run into problems or if there is a different/easier way to solve the problem. My SAQL knowledge is sufficient to get me to where I want to be, but I’m always keen to learn of easier ways and tips to improve.