In Part 1 we setup some basic measurements in Qlikview to summarize the Incidents for the previous month.
The method used to identify how many were opened or closed etc, was to use the Qlikview Scripting language in a rudimentary fashion, but one that was easy to read and applicable to most reporting software.
Now we are going to compare our results to the KPIs stored on the Database. This will also be done with the standard Scripting language.
There is a cool feature in Qlikview called Set Analysis, which can be used in this scenario. But for what we are doing here there is little benefit.
Set Analysis allows another method of identifying a particular group (or Set) of data to summarize. In the next tutorial, we will look at reporting historical service data on a monthly basis which makes full use of Set Analysis.
So, let’s set up our first criteria for Incidents that were responded to in time.
Measuring Elapsed Time
Now things get a little tricky!
The Interval() function which is used to find the difference between two date-times returns a value that is stored as a date-time.
I personally have found converting this result into an integer to be very problematic.
But the SLAs/KPIs are usually stored in the database as the number of minutes, which is also the case in our sample database.
So, rather than change the Interval() into a number, we shall represent the SLA as a numeric version of a Date-time Field. This is not as straightforward as with…any software you care to mention!
When a Date-time is turned into a number, whole digits are counted as days and Hours, Minutes and Seconds are treated as a percent of a day.
Therefore 0.5 is noon/12:00, ie: half a day.
And therefore 0.00694 is a minute.
From this, by multiplying the number of minutes in the SLA field on the database we can get a comparable value for the report. It is not neat, or pretty, but it works.
And more importantly, I have not been able to find any other way of doing this!
Applying the Calculations
Now we know how to make sense of the values in the SLA Fields in the database, we can identify how many Incidents were Resolved to in time and/or how many were Closed in time.
With these numbers, not only can we display the total of successful Resolved/Closed times, but we can easily calculate the success percentage for each.
We’ll look at the Resolved SLA in detail and expand our grid of summaries.
Resolved in SLA
Add a new Field to the Grid with the name: ‘Resolved in SLA’.
To determine whether an Incident has been Resolved within the SLA, we measure the elapsed time in Minutes between the Resolved Date and the Open Date (the first line of the code) then compare it to the Resolution Target Field which we modify into the same type of numeric representation (the last line of the code):
<=Resolution_Target * 0.000694, 1,0),0))
Resolved in SLA (%)
Add a new Field to the Grid with the name: ‘Resolved in SLA (%)’.
To find the percentage of the Incidents resolved successfully within SLA, we need to repeat the code of the previous summary to find the total of successfully resolved Incidents (the first two lines of code). Then, divide that result by the total number of resolved Incidents and multiple up into a percentage (the last line):
=num(((sum(IF(InMonth(Resolved_Date,today(),-1),if(interval(Resolved_Date-Open_Date,’mm’) <= Resolution_Target * 0.000694, 1,0),0)))
(sum(IF(InMonth(Resolved_Date,today(),-1),1,0)))) * 100, 00.00)
The NUM function is used to format the percentage to two decimal places.
Note: For simplicity’s sake (and because I created the dataset) I have not tested for any divide by zero errors. In real life, this is very likely to occur, especially if the organisation does not have many high priority Incidents or you are splitting the results by certain teams.
Obviously, to create the same metrics for the Closed in SLA it is a simple case of repeating the above, but with different database fields.
Color Coding for Clarity
Currently, the summary grid is readable but it does not highlight when KPIs are being breached.
Color coding certain cells within the table based of their contents is a quick way to draw the audience’s attention to any trouble spots.
The standard approach to this style of color coding is to use a RAG (Red, Amber, Green) color scheme.
For simplicity, we shall just use Green or Red to indicate whether a KPI has been reached or not.
On a personal note: I am not a fan of implementing an Amber color band and find it often creates more issues than it solves.
The Priority table field ‘KPI Resolution’ contains the target percent for each priority. We can use this field to control the background of a particular column.
There is a ‘Visual Cues’ tab in the table Properties which looks perfect for this job, but it does not work properly with database fields, just literal values which have to manually entered and apply to the whole column (rather than a dynamic measurement for a specific Priority like we need).
Instead, we shall use the ‘Background Color’ property which is accessible by expanding the ‘Resolved in SLA (%)’.
We can use the same code as for the ‘Resolved in SLA (%)‘ again to form most of the logic we need and wrap this up in an IF Statement in order to apply Green or Red color coding:
=if((num(((sum(IF(InMonth(Resolved_Date,now(),-1),if(interval(Resolved_Date-Open_Date,’mm’) <= Resolution_Target * 0.000694, 1,0),0)))
(sum(IF(InMonth(Resolved_Date,now(),-1),1,0)))) * 100,00.00)) > KPI_Resolution,rgb(128,255,128),rgb(255,128,128))
The result is a color coded ‘Resolved in SLA (%)‘ column:
The exact same process we have just used for Resolved column can be used for the Closed column. It is just a case of using the Resolved date instead of the Open date and the Closed date instead of the Resolved.
Once done, this can also be grouped together. This is all the information we need, but looks a little ‘off’.
I particularly don’t like the total appearing at the top of the table. It just looks wrong to me and always has (Crystal Reports does the same thing).
This can be changed easily by checking the ‘Totals on Last Row’ on the Presentation tab for the table Properties.
To improve the table as a whole, go to the various tabs, click around and try out the different settings.
Beyond that, a few tweaks to alignment and font and it looks a whole lot better:
COUNT DISTINCT vs SUM
I demonstrated both variations of aggregation in these tutorial, and due to the simplicity of the dataset, both are as good as each other.
Anyone with a decent level of SQL experience will know that when more tables are added it is only a matter of time before parts of the returned data become duplicated.
The COUNT DISTINCT based on the Incident_ID removes all these worries by only counting each Incident_ID once.
There is a third option: using Set Analysis to pick out the required data. But in this situation, it suffers from the issue as the SUM example and cannot detect repeated data.
Obviously, none of this is a concern when working with Excel spreadsheets when the data tends not to be linked, but databases have additional requirements which need consideration.
Fitting It All In
Depending on the number of metrics associated with Incidents, this table can get a little too large and unclear.
Qlikview has an elegant solution to this problem.
Now, simply drag the ‘Resolve in SLA (%)’ over the ‘Resolve in SLA’, then drag the ‘Resolved’ Field onto either of those two.
Repeat the process for the Closed summaries and you will see this:
Click ok and the user can now select whether to see the count of Incidents resolved in SLA or the percent.
By repeating this simple maneuver and stacking the three Closed Fields together, we can turn this:
This is a fantastic solution: as long as the target audience is aware they can cycle through the Columns, which is a far more elegant solution than an endless grid of data.
That wraps up the tutorial on how to create this data grid, which is a mainstay of ITIL Service Management reporting. This example has been based on Incident Management, but the techniques can be easily adapted to reporting on Problems, Changes, Service Requests or Availability.
I may revisit this tutorial and add a third installment on how to deal with measuring elapsed time only during Service Support Hours, rather than the 24 hour clock I have assumed here for brevity.
The natural next step for ITIL reporting is to add context to the one month which is the focus of the report. This normally takes the form of showing the monthly history for a period of six to twelve months.
Several values are usually plotted on the same graph and this is a challenge in its own right as it goes against the usual charting/grouping logic.
This is where Set Analysis comes in particularly handy and will form the basis of the next tutorial.