Learning Qlik Sense The Official Guide (2015)

Chapter 11. Travel Expense Discovery

The goal of this chapter is to continue our exploration of Qlik^® Sense with real data, and how it meets the needs of business discovery in your organization. The Qlik Sense application chosen for this chapter is a topic near and dear to most finance departments:Travel Expense Management. Like all the applications covered in this book, please feel free to explore this application live at http://sense-demo.qlik.com. With that said, let's turn our attention to the following challenge of travel expense management and how Qlik Sense addresses this common business challenge.

In this chapter, we will cover the following topics:

· Common travel expense analysis challenges

· The unique way Qlik Sense addresses these challenges

· How the application was built

The business problem

Expenses are a major line item of every global company. Traveling cost is a part of every sales and service cycle. Unfortunately, most expense tools capture the transaction but do little to help gain insights about how the expenses were spent, when, and most importantly how, to reduce these expenses when possible. Some key questions can include:

· How are expenses tracked verses budget?

· What is the actual amount spent to date?

· What is our largest expense type?

· How can we reduce expenses?

Application features

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 5, Empowering Next Generation Data Discovery Consumers.

Key questions can include:

· How are expenses tracked verses budget?

· What is actual amount spent till date?

· Is my department over budget?

· 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 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.

Tracking expenses

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.

How are expenses tracked versus budget?

Analyzing expenses overspend

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.

What is 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:

What is the Average Employee expense verses Average US Meals expenses?

Digging deeper into the data

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:

Which employees 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:

Which employee exceeds the average for US Meals per diem?

Creating an analysis story for travel expenses

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 6, 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.

Creating an overview

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:

Sharing our analysis

Now that the overview of the analysis is complete, let's move on 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:

Finishing the story

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 6, 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.

Developing the application

Let's start our review of the heart of a Qlik Sense application: the data model. As you can see from the following screenshot, there are six tables in the Travel Expense Management associative model. At the heart of this application is the Expenses table. These tables were created through Data Load Editor, which was covered in Chapter 7, Creating Engaging Applications. It is worth noting that Qlik and Qlik partners provide both general-purpose connectors and specialized connectors to access a broad array of data sources.

Examining the key tables

Let's examine the key tables.

Expenses

The Expenses table contains all the key information (shown in the following screenshot) about the expense transaction of an employee. This includes information such as the date, employee name, expense type, and so on.

The Expenses table

PerDiemRates

The PerDiemsRates table contains all the key information (shown in the following screenshot) about state, city, month, rates, and so on:

The PerDiemRates table

Airfare

The Airfare table contains all the key information (shown in the following screenshot) about the origin, destination, airfare value, and so on:

The Airfare table

Department

The Department table contains all the key information (shown in the following screenshot) about the department ID, type, and department name:

The Department table

Budget

The Budget table contains all the key information (shown in the following screenshot) about the budgeted amount using a compound key value that includes the expense type, department ID, and date:

The Budget table

LinkTable

The LinkTable table contains all the keys (shown in the following screenshot) to link the expense, department, and budget tables:

The LinkTable table

Dimensions

Now let's turn our attention to what has been exposed in the Travel Expense Library by the developer to facilitate the creation and sharing of personal sheets. In the following screenshot, we can see the dimensions that were created. One particular dimension that is worth calling out is the Expense dimension, which provides a drill navigation from ExpenseCategory to ExpenseType. This capability usually requires extensive modeling or complex scripts in other BI software products, but with Qlik Sense, this is a simple selection process when creating the dimension. This is another example of the power of Qlik's associative indexing engine in action, but this time, easing the development of navigation within the application.

Dimensions

Measures

The next area to cover is Measures. These are calculated expressions that most often form the KPIs within an application. In the following screenshot, we can see a list of measures that are used and exposed to contributors to allow them to create private sheets. Note that hovering the pointer over any of these objects makes a preview popup appear to provide additional context. In this case, you can see how the measure Actual - Food is calculated.

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. Please refer to the Qlik Sense online help for additional information on the Qlik Sense function, which is available at https://help.qlik.com.

The measure expressions include:

Visualizations

The last category of objects in the Library (Master items) is Visualizations. These are preformed visualizations that are typically the most popular or requested. They are defined to help facilitate a user's analysis and can be easily dragged and dropped onto a private sheet. In the following screenshot, we see a horizontal bar chart that analyzes the variance in Booked Airfare in Advance vs Not in Advance. Each of these visualizations contains predefined dimensions, measures, and chart definitions.

The Travel Expense visualizations

Summary

In summary, Qlik Sense provides unique capabilities to meet the challenging task of analyzing and managing travel expenses. Without the capabilities offered by Qlik, this task can be difficult due to the size of the data and the many perspectives that can be taken in trying to understand airline purchasing, meal expense habits, and the impact on meeting corporate budget requirements. Qlik's associative indexing engine powers this exploration and means that meeting these requirements is no longer challenging at all.

In the next chapter, we will explore how Qlik Sense meets the needs of demographic data discovery.