Learning Qlik Sense The Official Guide (2015)

Chapter 10. Human Resource Discovery

Just like the previous chapter, this chapter will show you how to apply Qlik^® Sense to the challenges of analyzing real data. This chapter's example and many others are available for you to explore on http://sense-demo.qlik.com. Again, make sure you bookmark this link, as more demonstrations and examples are constantly being added and updated.

This chapter is about the analysis of Human Resources data, and it covers the following topics:

· General information about common KPIs

· What a typical data model could look like

· An example of how to use the global selector

· Examples of dimensions and measures

The business problem

The term Human Resources analysis covers a wide area of KPIs that use data from a number of different data sources.

It could be that you want to analyze in-house data, for example, the efficiency of the recruitment process and the costs tied to it. It could just as well be analysis of external data, for example, different employee surveys or sentiment analysis on social media.

Just to give you an idea, we have compiled a list of some of the most common areas to investigate when preparing a Human Resources analysis:

· Recruitment: This measures the efficiency of the recruitment process; for example, what is the recruitment cost per employee? What is the average lead time to recruit?

· Employee satisfaction and retention: This measures employee loyalty; for example, what is the average satisfaction (as measured by a survey)? What is the employee turnover?

· Training: This covers the following questions as examples: what is the total expenditure on training? What percent of the employees have gone through the training? What is the number of training hours per employee?

· Health and Safety: This covers the following questions as examples: what is the number of accidents per year? How many employees are of adequate health and get safety training? How much are health and safety prevention costs?

· Career and Compensation: These cover the following questions as examples: what is the average salary rate? How does it compare to the national average? How much are the salary costs compared to the sales turnover? What is the cost of social and medical insurances?

All of the preceding KPIs can be split by a dimension, month, department, position, tenure, age, and so on.

It might be that you don't have data for all the preceding KPIs, but we can assure you that if you do, you will find it worthwhile to analyze them.

Application features

On our demo site, we have a human resource app. You can find it on http://sense-demo.qlik.com, by clicking on the Human Capital Management link. In it, you will find a subset of what you can analyze in HR data. Mainly, it analyzes training investments and employee satisfaction.

When you open the app, you will first see the app overview, with a small description of the app and a thumbnail in the form of a small bar chart, as shown in the following screenshot:

The overview of the Human Capital Management application

Below this overview, you will see a number of sheets. These are created according to the dashboard analysis report principles described in Chapter 4, Overview of a Qlik Sense Application's Life Cycle. This means that the leftmost sheet is an overview, very much like a dashboard, whereas the other sheets are prepared for analysis and detailed information.

If you click on the Stories button to the left, you will see that the app also contains one story—a story that can be used to present the data in the app. It can also be used as an introduction to the app the first time you open it.

The sheets on the app overview page

Sheets

The first sheet is called Dashboard, and if you open this, you will see several key numbers, a couple of charts, and a map. This overview is designed so that you can quickly get a brief understanding of the information without having to make any selections.

The first sheet – Dashboard

The top-left chart and the map show the number of employees per role and per country. The two bottom charts show the number of hires, number of terminations, and total compensation over time. Note that this sheet does not contain any filter panes because it should not encourage making selections.

The other sheets contain more detailed information, ordered by topics, such as Demographics, Ethnic Diversity, and so on. The final sheet contains a table only, showing the details about what the application captures, should the user be interested in drilling down to the lowest level.

Training costs

This application contains information that covers only some of the KPIs mentioned in the previous section. One area that it covers well is training. Hence, our first question could be, what is the total expenditure on training? The answer to this can easily be found from the Employee Development sheet.

The Employee Development sheet

In the top-right part of the sheet, you can find a textbox containing Total: under Training costs.

The next question is, what is the percentage of employees who have gone through the training? The answer to this can be found from the same sheet. All charts on this sheet show the training completion ratio, split per department or program. By clicking on a chart, you can drill down to the data and explore how the numbers vary between departments, programs, job, course type, and course name.

When you analyze data in a Qlik Sense application, you will realize that there are many ways of using it. For example, say that you want to look at the training progress by gender, to see whether there is any difference between men and women. You have already found the charts showing training progress on the Employee Development sheet, but these only show the progress by department and by program.

Chart showing training progress by department

Using the global selector

If you have been authorized to create your own visualizations, you can simply go to edit mode, drag Gender onto the chart, and replace the existing dimension with Gender.

Even if you aren't allowed to change anything, you can still do your analysis. You just have to do it in a different way. What you could then do is use the global selector (to the right in the Selections bar) to select Female first, and then Male, shown as follows:

Selecting Gender in the global selector

When you now close the global selector and return to your Employee Development sheet, you can toggle between Male and Female using the Step back and Step forward buttons to the left in the selections bar, as shown in the following screenshot. There's also a Clear all selections button in this bar:

The left part of the Selections bar, with the Step back, Step forward, and Clear all selections buttons

This way, you can see how the chart changes as you toggle between the data of male and female employees.

The next question could be about employee compensation. For this, you need to go to the Employee Performance sheet. Here, you will find a table showing all employees and the compensation attached to them. By clicking on Avg Compensation: in the Total / Avg Salary by employee rating chart, you can sort the employees in ascending or descending order, and can thus get a good overview of the span.

As users, we would probably also want to see a chart showing the average and total salary costs, split by department, but unfortunately, this has not been created by the app developer. However, in a real-life situation, a user should be empowered to create such charts. After all, it is impossible for an application developer to foresee all the needs of a user. Hence, this is a good example of the need for self-service data discovery.

How the application was developed

The data model for the Human Capital Management application looks like what is shown in the following screenshot:

There are six tables in this application:

· Employees: This is the main table, which has one record per employee and month. It contains all of the relevant information about the employee such as country, position, salary, and so on. It would probably be possible to normalize this table into one table containing employee information that doesn't change over time and another table with the time-dependent information.

However, since the Qlik^® engine analyzes the data just as efficiently either way, we don't see any great benefit in spending time structuring the data more.

Preview of the Employees table

· Hierarchy: This table contains information about the manager of the employee.

· Survey: This table contains the results from an external survey made on employee satisfaction. Such surveys are usually made once in a year, so if the results from several surveys are kept in this table, the key needs to hold information not only about the employee, but also about the year in which the survey was made.

· Training: This table contains information about the training sessions attended. Hence, if an employee has attended two courses, two records are stored. The table also contains costs associated with the training sessions.

· Courses: The possible courses are stored in this separate table.

· Map shapes: This table lists all countries. It has one record per country and could, in principle, hold demographic information about the country. However, in this case, it only holds the map information—the shapes of the countries—used in the map object, which is in the user interface.

Note that this application has fields used for measures in several tables: salary can be found in the Employees table, cost for training can be found in the Training table, and ratings from the survey can be found in the Survey table. This is in sharp contrast to classic BI tools, where all such facts need to be in one single table—the Facts table.

Dimensions

There are many fields that can be used as dimensions, and a large number of them have been added as dimensions to Library such as Employee Name, Age Group, Department, and so on.

In principle, any field that a user would be able to use as a grouping symbol should be added as a dimension. However, you should not add cryptic keys or numbers that could be used as measures.

The dimensions in Library

Measures

A number of measures have also been defined, for example, # of Employees, # of Women, Attrition, # of New Hires, Avg Compensation, and so on.

It is important that the app developer writes the formula correctly, since this is something that could be difficult for a business user. A business user doesn't always have knowledge about the data model, which is something you need in order to get all the expressions right.

In the following table, you can find some of the measures defined in this app:

Finally, there are also a number of visualizations added to Library. These are important, as they help a business user in the initial use of the app.

The most common bar charts and tree maps have been stored here: Number of employees by role, Number of employees by management position, and so on.

The list of visualizations in Library

Summary

In summary, the analysis of human resource data is easy when you use Qlik Sense's unique capabilities. Such an analysis can otherwise be difficult due to multiple and disparate data sources holding human resource data. Qlik's associative indexing engine powers this exploration, and analysis is made easy for the user.

In the next chapter, we will look at how Qlik Sense can be used to analyze costs, or more specifically, travel expenses.