Normally, we would use the class() function to create a histogram; however, the class() function generates bins from existing values. A bin without any corresponding value behaves in the same way that missing or null values behaved in Chapter 12, Sales Perspective. Although we are not affected by this behavior when we use a bar chart, we are not so lucky when we work with a line chart. The line jumps from one existing bin directly to another without representing the missing bins as zero.

Therefore, we use the valueloop() function rather than the class() function because valueloop() is a list of numbers that have no relationship with the data. We define the range of valueloop() dynamically with the min() and max() functions and the bin size is determined by the value of the vEmployeeAgeBinSize variable. The floor() function helps to round down Employee Age so that we can assign it to the proper bin. We usually use floor() to round down to the nearest one, but we can also use it to round down to the nearest five, ten, or thousand.

Finally, we add a second degree polynomial trend line to get a general idea of the shape of the distributions. As both resemble a normal distribution, this trend line does give us a general idea of the distribution. We can observe that the Wisconsin office in general has a greater number of young employees and fewer middle-aged ones. If the distribution does not resemble a normal distribution, we can try using trend lines of different degrees or, more simply, enlarge the bin size. Notice how different the distributions look without the trend lines and with an Employee Age Bin Size of 5:

We could create a simple box plot using Box Plot Wizard in the file menu, Tools; however, in this case, we are limited to using one dimension. As a workaround, we use a combo chart with bars and error bars to create a box plot. As a part of this technique, we have to consider the fact that QlikView will draw the same number of error bars as there are expressions. As we have three distinct values in the Employee Current Office field, we add a third, dummy expression that is not visible because the value is zero. If the second dimension has five distinct values, we create a total of three dummy expressions.

Although this trick may seem awkward at first, the chart is not effective when the second dimension has a large number of distinct values. Therefore, we never expect to choose one with more than five or so values and it is not much trouble to create three dummy expressions.

The last adjustment involves the maximum value of the expression axis scale. The maximum that QlikView automatically calculates is unnecessarily large. So, we dynamically calculate the maximum plus a ten percent cushion. This makes it easier to compare the different box plots.

This chart is simple to create because much of the work is done in the script. We create a new row in the fact table for every dismissal and new hire. The Dismissal and Hiring fields contain the value, 1, so that we only have to sum them up to discover the total occurrences of each event. Also, every time we add to the employee payroll event, we calculate how many months have passed since they started working for the company.

Sick days will tend to increase as the team grows; so, instead of basing our heat map on the actual number of sick days, we calculate sick days as a percentage of the total number of employee working days. According to the chart, after a lull in sick days in the beginning of 2015, there seems to have been an increase in the later part of the year. We can also compare the different offices by adding [Employee Current Office] as a dimension and placing it above the Year dimension.

Let's create the following bar chart:

-sum({$<_Employee_Active_Flag={0}>} Training) 

-count({$<Training={1},_Employee_Active_Flag={1}>} 
distinct 
   if([Employee Historical Job Title] 
           <> [Employee Current Job Title]and [Employee Historical Office]
           <> [Employee Current Office],[Employee ID]))

-count({$<Training={1},_Employee_Active_Flag={1}>} 
distinct 
   if([Employee Historical Job Title] 
           = [Employee Current Job Title]and [Employee Historical Office]
           <> [Employee Current Office],[Employee ID])) 

sum({$<_Employee_Active_Flag={1}>} Training)-[Promotion]+[Promotion / Transfer]+[Transfer]

count({$<Training={1},_Employee_Active_Flag={1}>} 
distinct 
   if([Employee Historical Job Title] 
           <> [Employee Current Job Title]and [Employee Historical Office]
           = [Employee Current Office],[Employee ID]))

The Training={1} set analysis filters the data model, so we only see each employee's historical job title and office at the time he or she was trained. In this data model, we constantly update the current job title and office in every employee record so that we can always compare it with the historical records.

We use an if-statement within the count() function in this example in order to highlight how to compare fields of different SCD types. If we are dealing with a large amount of data, then we migrate this if-statement to the script and create a flag in the data model that indicates which dimension attributes have changed. We can then use this flag in the expression's set analysis.

In the chart, we observe that both offices suffer from a large number of dismissals after training employees and employees in the Wisconsin office tend to transfer when they earn a promotion. This example demonstrates why it is important to understand how the data model handles slowly changing dimensions and how we use both SCD Type 1 and SCD Type 2 to create an insightful analysis.