As this is my first Qlikview post, it feels like some sort of introduction is required.
If Crystal Reports and Xcelsius had a baby, which evolved and improved over many generations, it would be called Qlikview.
All the power of SQL and its own scripting language, mixed with more interactivity and drilldown functionality than you could ever want.
Qlikview is fantastic.
ITIL is fantastic.
So here is a straightforward tutorial on how to build a simple ITIL Incident throughput and KPI table in Qlikview.
This post actually follows on quite nicely from my previous one, as it focuses on extracting several cuts of data in the most efficient and accurate way.
This is the result we are aiming for…
…though we will add some formatting to allow a user to select which columns they want to see and shorten the number of columns…
…which is often the key focal point of an Incident Dashboard, or a separate report with a couple of complementary charts.
To keep this tutorial short and sweet (in two parts!!!), the table does not include some of the columns often associated with this style of report. Things like: Incidents on hold, re-opened, cancelled or transferred to another Business Unit.
If any of those columns are added, it can impact the integrity of the existing information as it subtly changes the context of the entire report. So think carefully before doing so.
This tutorial is based on a simple Access Database which has only three tables, but the examples I work through should be easily applied to any ‘real life’ Service Management application:
If you’d like the actual Access database I use here, drop me an email (email@example.com). To follow this tutorial exactly, you will need both this database and to set up an ODBC driver pointing to it.
What this style of report shows
The most basic version of this report shows the volume of Incidents flowing through a one month period.
Essentially, if the ‘Brought Forward’ value is consistently less than the ‘Carried Forward’, the responsible Business Unit is struggling to keep up with demand.
We will develop this part of the report before extending it to include SLAs.
Getting the Data
The trick with this style of report is to get all the data required for accurate results while keeping the actual volume of data to a minimum.
I have found that if separate Tables are added to Qlikview via individual SQL, something odd happens with the linking and the results can be questionable.
With this in mind, the main body of the data for this report is going to be gathered by one SQL statement.
From this one set of data each Incident will be counted one or more times as its criteria is tested for each summary column in the report.
1. Open Qlikview and open a new report.
2. Go to the menu File -> Edit Script.
3. Click on the Connect button to point Qlikview at the database.
4. Select your database from the list. In this case it is ‘Dove_ITIL’.
5. Enter the following SQL beneath the newly generated line of code for the ODBC:
FROM Incident INNER JOIN Priority ON Incident.Priority_ID = Priority.Priority_ID
Incident.Closed_Date IS NULL
Incident.Closed_Date >= DateValue(Year(Now()) & ‘,’ & Month(Now())-1 & ‘,01’)
Incident.Open_Date < DateValue(Year(Now()) & ‘,’ & Month(Now()) & ‘,01’);
6. Click on the Save icon at the top of the window, and save the report.
7. Click Reload icon to populate the data into the report.
The report now has access to the data we need to build the table.
Displaying the Data
With the subset of data selected, we need a container to hold the summary information required to create the ITIL stats.
In this case, we are going to use the Straight Table option within the Charts.
8. Click the Chart icon.
9. Enter a title and select the ‘Straight Table’ icon:
10. Click Next.
Dimensions / Grouping the Data
Because we are interesting in the SLAs for each different level of Priority we shall use this as a Dimension to focus our summaries.
11. Select the ‘Priority ID’ and click on ‘Add’ to move it to the ‘Used Dimensions’ panel.
12. With the field still highlighted, click on the ‘Edit…’ button, and type the following code:
=Priority_ID & ‘ – ‘ & Priority_Name
This will display both the Priority ID and Name together in the table and make it more meaningful.
13. In the Label, even though it is greyed out, type ‘Priority’ for the columns title.
14. Click ‘Next’.
The next screen automatically opens an Expression Edit window for the creation of one summary.
We shall add the first column now, save and check the report, then add the additional summaries.
First things first: the table starts with the number of Incidents which were opened before the start of the target month and are still open as the month begins.
There should be an Expression window open from step 13…
15. Enter the following code:
=SUM(IF(Open_Date < AddMonths(MonthStart(ToDay()),-1), IF(isnull(Resolved_Date) OR Resolved_Date >= AddMonths(MonthStart(ToDay()),-1),1,0),0))
What does it do?
Well, by using ToDay() to find the current date, MonthStart() to wind the time back to the beginning of the current month and AddMonths() to move an entire month at a time we can calculate everything we could ever want for monthly report.
In this case, we want Incidents opened before the target month that were resolved during or after the month (or not resolved at all). We put those conditions into an IF statement that evaluates to 1 if they are true (0 if not) and then SUM the 1s for the result.
16. Click ok to save the Expression and enter ‘Brought Forward’ as a Label for the Expression :
17. Click Finish.
The result should look a lot like this:
It is certainly not the most impressive thing ever created in Qlikview, but is a step in the right direction for this ITIL table.
From this point we can edit the table and add the additional rows. For the reminder of Part I of this tutorial we shall focus on more basic summaries, similar to the Brought Forward column.
But first, we shall fix the Sort Order.
18. Right click on table and select ‘Properties…’.
19. Click on the ‘Sort’ tab and tick the ‘Text’ Sort By option.
20. Click on the ‘Expressions’ tab to be ready for the next part of the tutorial.
This is a count of all the Incidents which are opened in the previous month and is one of the easiest to calculate.
21. From within the Expression tab, click ‘Add’ and enter the following code:
This code works a little differently to the previous example. The IF Statement returns the Incident ID if true (and nothing if it does not) and these are then COUNTed.
22. Enter ‘Opened’ as the Label for this expression.
This is a count of all the Incidents which are resolved in the previous month and is very similar to the Open Incidents in how it is calculated.
23. Add another Expression with the following code:
24. Label this Expression as ‘Resolved’.
Closed Incidents, the most annoying part of Service Management: getting an official sign-off of a completed job from a user who no longer cares as they are now happily working again.
This is a count of all the Incidents which are closed in the previous month and is very similar to the previous two columns in how it is calculated.
25. Add another Expression with the following code:
26. Label this Expression as ‘Closed’.
And lastly (for the moment) the number of Incidents which were opened before the end of the target month and are still open as the month ends.
This calculation is almost the same as the Brought Forward summary we started with, and when viewing successive months: the Carried Forward from the previous month should equal the Brought Forward of the next month.
27. Add another Expression with the following code:
=SUM(IF(Open_Date < MonthStart(ToDay()),IF(isnull(Resolved_Date) OR Resolved_Date >= MonthStart(ToDay()),1,0),0))
28. Label this Expression as ‘Carried Forward’.
29. Save the changes and view the Table.
And here it is:
Summary (so far)
These five columns provide the volume of Incidents and their throughput during the month. Even if we stopped the development now, this report has some value.
As well as showing the volume of Incidents by Priority, it is easy to see if the Business Units responsible are coping or not, just by comparing the Brought Forward and Carried Forward fields.
Which is just as well, because I am going to end this tutorial here!
Part II is coming soon! This will cover the SLA column scripts, formatting, tweaks and review the report as a whole.
Sign up on the right to get an email when it is available.