Now, let's take a look at the unique way Qlik Sense approaches solving the business problems mentioned in the previous section. Qlik Sense's associative model allows users to answer common questions through filters, but they can also address the more important follow up questions that arise. As you may recall, this type of analysis uses "The Power of Gray", named after the color Qlik Sense assigns to nonassociated elements (potential opportunities for improvement) highlighted in Chapter 3, Empowering Next Generation Data Discovery Consumers.
The key questions include:
- How are expenses tracked versus the budget?
- What is actual amount spent to date?
- Is my department over budgeted?
- What is our largest expense variance?
- What is the meal expense breakdown?
- How can we reduce expenses?
Before we begin, let's review the main sheets within the Travel Expense Management application. As noted in the following screenshot, the application is made up of three sheets: Dashboard, Airfare, and Food Expenses:
The Travel Expense Management overview
These sheets provide insights into the overall expense management, and the two largest expense categories of airfare and food. With that said, let's now turn our attention to our first question.
A key question is how to manage departmental expenses on a quarterly basis. How are expenses tracked versus what has been budgeted?
In the following screenshot, we can see in the sample application that Total Expenses is below budget by $30,964. This is good news. Additionally, we see that the largest expense is Airfare, and what is more troubling is that Food Expenses is running $6,679 over budget.
Expenses tracked versus budget
Taking a closer look at food expenses such as breakfast, lunch, and dinner, we can see that for most of 2013 (11 out of 12 months), employees spent more for meals than was budgeted. Exploring a little deeper, we can see that as you'd expect, Dinner takes up the majority of the expenses with $30,735. What's more interesting is that the budget to actual variance starts to sharply grow in May, October, and November.
The meal expense breakdown
Now that we have highlighted a problem with food expenses, let's start to use the more detailed information that is available in the Food Expenses sheet, shown in the next screenshot. Additional external information is always helpful in variance analysis. In the following screenshot, we can see not only Average Employee Daily Meal Expenses vs Average US Per Diem Meal Rates (external source), but also Average Employee Daily Meal Expenses vs Average US Per Diem Meal Rates (external source) by Employee on a monthly basis:
Average Employee expense versus Average US Meals expenses
Overall, the company seems to be performing well against the US average, but let's dig a bit deeper. For example, are there employees that do not spend on meals, which could be lowering the company average? To find this out, simply select the global filter (also known as the Selections tool) icon, as shown in the following screenshot:
Global filter
Using "The Power of Gray" (nonassociated elements), we can see in the Employee dimension that four employees do not spend on their meals, as shown in the following screenshot:
Employees who are not spending on food
Knowing this, we can exit the global filter screen and continue our employee meal analysis. What started off as a travel expense analysis has, through Qlik Sense, narrowed down the analysis to an employee meal analysis. As we scroll through the employees, as shown in the following screenshot, we immediately get to an employee (Ileen Menard) who has exceeded the average US per diem allowance significantly, and by selecting Ileen Menard, you can see that May was the month with the significant variance. Additionally, the green/white/gray is shown in summary on the selection bar after the dimensions/members are selected in the global filter below:
Employees that exceed the average for US Meals per diem?
Now that we've completed our analysis, let's create a Travel Expense Analysis story so that we can share our findings through our organization.
In Chapter 4, Contributing to Data Discovery, we reviewed the role of a contributor, and how to create a Qlik Sense story and publish it so that others may view their analysis. Based on the analysis discussed in the previous section, the travel expense story is made up of three sheets.
In the Overall Expenses sheet shown in the following screenshot, you can see the In 2013, the company overall stayed below budget by almost $31,000. While Travel expenses were well below budget, food expenses were $6,679 above budget (17%) annotation as well as the key snapshot gauges of the actual to budget performance total, Travel Expenses, and Food Expenses:
Now that the overview of the analysis is complete, let's move onto the next step and share what was found in analyzing Food Expenses. The Food Expenses story sheet, shown in the following screenshot, highlights that for most of 2013 (11 out of 12 months), employees spent more for meals than was budgeted:
With these two story sheets defined, a final sheet for the story will require a bit more interaction for the viewer. As noted in Chapter 4, Contributing to Data Discovery, this is achieved by embedding the Food Expenses sheet directly into the story, as shown in the following screenshot. This will allow the author to not only narrate the findings but also invite the viewer to explore these findings and others within the application:
With the story created, the final annotation makes the following recommendation: As a company, we should consider increasing the budget for meal expenses. Most of our employees expensed less that the per diem rate in 2013 and were significantly above budget in their meal expenses so increasing the budget seems like a good idea.
Now that we have covered the application features, let's turn our attention to how it was built.