Human Resources costs can represent up to 70 to 80 percent of the total cost of doing business (Lawler and Boudreau 2012). Our first goal is to analyze headcount, payroll, and how much revenue (or profit) we generate per employee and payroll dollar spent.
We start our analysis by comparing headcount and payroll. As these amounts use a different scale, we use the left axis of a dot plot chart for headcount and the right axis for payroll. Before beginning the following exercise, we import this chapter's exercise files into the QDF as we did in Chapter 2, Sales Perspective.
- In
1.ApplicationHR_Perspective_Sandbox.qvw, let's create a combo chart that measures headcount and payroll by year-month, as follows:Dimensions
Details
Label
Value
Year-Month
Year-Month
Expressions
Label
Value
Headcount
count(distinct [Headcount Counter])
Payroll
sum([Monthly Salary])
- In the Expressions tab, enable only Symbol in the Display Options section. Define Headcount as Dots and Payroll as Circles.
- In the Axes tab, disable the Forced 0 option for both expressions. Select Payroll and enable the Right (Top) option in the Position section.
- Adjust the look of the chart accordingly.
When we use a dual axis and remove the Force the axis to zero option, QlikView automatically aligns the maximum and minimum values of each metric so that they are at the same height along the Y-axis. We can, therefore, compare the percentage growth of the two metrics. For example, in the Headcount and Payroll chart, both Headcount and Payroll reach their minimum value in December 2014. At the end of 2015, Payroll is higher along its axis than Headcount, so we can conclude that it grew at a faster rate than Headcount. In other words, it implicitly shows an increase in the average payroll per employee.
Also, as we are more interested in the trend of the two metrics than the actual amounts, we can avoid forcing the axis to zero. It is sometimes useful to do this when we are working with a dot plot or a line chart. However, we should be careful to avoid doing the same to bar charts because the length of a bar traditionally represents the total actual value.
Let's apply the same method to compare these two metrics with the company's revenue and determine our personnel's productivity. The first formula compares revenue and headcount:
Revenue per employee = total revenue divided by total headcount
The second formula calculates employee productivity in terms of actual cost:
Employee Productivity = total revenue divided by total payroll
The ideal result of these metrics differ by industry, so we should compare our results with businesses from the same industry. Within our own company, we can also compare the results of different offices or branches. In order to do so, we create a dot plot chart that is similar to the previous example, but use the Trellis option so that we can create a series of the same chart by office. Every chart in this series uses the same axis scale so that we can compare the results of different offices as if they were located in only one chart.
- Let's create a combo chart that measures headcount and payroll by year-month.
Dimensions
Details
Label
Value
Office
Office
Year-Month
Year-Month
Expressions
Label
Value
Revenue per Employee
-sum({$<[Account - Concept]={"Total Revenue"}>} [GJ Amount])/count(distinct [Headcount Counter])Employee Productivity
-sum({$<[Account - Concept]={"Total Revenue"}>} [GJ Amount])/sum([Monthly Salary]) - In the Dimensions tab, click Trellis… and enable the Enable Trellis Chart option. Fix the Number of Columns to
2and the Number of Rows to1. - In the Expressions tab, enable only Symbol in the Display Options section. Define Revenue per Employee as Dots and Employee Productivity as Circles.
- In the Axes tab, disable the Forced 0 option for both expressions. Select Payroll and enable the Right (Top) option in the Position section.
We can easily see that the office in Iowa is more productive than the one in Wisconsin when the chart uses the same axis scale. Given that the Iowa and Wisconsin offices have equivalent departments and employee functions, the next step is to break down our personnel productivity and analyze why one office may have higher productivity than the other. We begin this process by visualizing our employees' profile and actions.