Qlikview ITIL Incident Management Report (pt 1)

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.

And…

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 (jasondove@hotmail.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:

SELECT Incident.Incident_ID,
Incident.Open_Date,
Incident.Resolved_Date,
Incident.Closed_Date,
Priority.Priority_Name,
Priority.Priority_ID,
Priority.Resolution_Target,
Priority.Close_Target,
Priority.KPI_Resolution,
Priority.KPI_Close
FROM Incident INNER JOIN Priority ON Incident.Priority_ID = Priority.Priority_ID
WHERE
(
Incident.Closed_Date IS NULL
OR
Incident.Closed_Date >= DateValue(Year(Now()) & ‘,’ & Month(Now())-1 & ‘,01’)
)
AND
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.

Brought Forward

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.

Open Incidents

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:

=COUNT(distinct IF(InMonth(Open_Date,ToDay(),-1),Incident_ID))

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.

Resolved Incidents

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:

=COUNT(distinct IF(InMonth(Resolved_Date,ToDay(),-1),Incident_ID))

24. Label this Expression as ‘Resolved’.

Closed Incidents

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:

=COUNT(distinct IF(InMonth(Closed_Date,ToDay(),-1),Incident_ID))

26. Label this Expression as ‘Closed’.

Carried Forward

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.

Advertisements

About jasondove

I am a consulting BA, with a heavy leaning towards process mapping and Business Intelligence.
This entry was posted in ITIL, Qlikview, ServiceNow and tagged , , , , . Bookmark the permalink.

2 Responses to Qlikview ITIL Incident Management Report (pt 1)

  1. Bogdan Popa says:

    What happens if you one ticket is opened in priority 3 (Medium) and during it’s resolved the priority is raised to Priority One for example and is closed as a Priority One ticket? How can you have an accurate report on the percent of tickets resolved in SLA?

    • jasondove says:

      Very, very good question!

      Unfortunately the answer is: ‘depends’.

      And it can depend on quite a few things. One of the biggest issues with Priority changes is that they are often not recorded in the database. If this is the case, every other point is irrelevant…we can’t report on what isn’t there.

      At the time of writing, my current assignment requires me to access the audit tables to track any changes.

      So, assuming we have access to the data, the next question is what to do with it?

      Well, it depends!

      This time it depends on the UC covering the service or team being reported on and what the clients want to see in the report.

      At the very least, a count of how many records at each Priority level used to be of a lower Priority.

      Why lower? Because it is mitigation on why Response and Resolution SLAs may have breached.

      The next step up from this, is to also include a column stating how many of these records did fail their SLAs. But it is possible that an agreement is in place with the Vendor/team that Priorities that become less important are seen to cancel out the others and it all averages out.

      The real challenge, and one I have battled with recently, is when the elapsed time needs to be calculated differently for each Priority change during the life of the Incident and the software doesn’t handle this making a bespoke solution necessary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s