Book Image

Qlik Sense: Advanced Data Visualization for Your Organization

By : Henric Cronström, Ferran Garcia Pagans, Neeraj Kharpate, James Richardson, Philip Hand
Book Image

Qlik Sense: Advanced Data Visualization for Your Organization

By: Henric Cronström, Ferran Garcia Pagans, Neeraj Kharpate, James Richardson, Philip Hand

Overview of this book

Qlik Sense is powerful and creative visual analytics software that allows users to discover data, explore it, and dig out meaningful insights in order to make a profit and make decisions for your business. This course begins by introducing you to the features and functions of the most modern edition of Qlik Sense so you get to grips with the application. The course will teach you how to administer the data architecture in Qlik Sense, enabling you to customize your own Qlik Sense application for your business intelligence needs. It also contains numerous recipes to help you overcome challenging situations while creating fully featured desktop applications in Qlik Sense. It explains how to combine Rattle and Qlik Sense Desktop to apply predictive analytics to your data to develop real-world interactive data applications. The course includes premium content from three of our most popular books: [*] Learning Qlik Sense: The Official Guide Second Edition [*] Qlik Sense Cookbook [*] Predictive Analytics using Rattle and Qlik Sense On completion of this course, you will be self-sufficient in improving your data analysis and will know how to apply predictive analytics to your datasets. Through this course, you will be able to create predictive models and data applications, allowing you to explore your data insights much deeper.
Table of Contents (5 chapters)

Chapter 12. 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, 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 include:

  • How are expenses tracked versus the 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 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:

Application features

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.

Tracking expenses

Expenses tracked versus budget

Analyzing expenses overspent

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.

Analyzing expenses overspent

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:

Analyzing expenses overspent

Average Employee expense versus 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:

Digging deeper into the data

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:

Digging deeper into the data

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:

Digging deeper into the data

Employees that exceed 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 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.

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

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.

Tracking expenses

Expenses tracked versus budget

Analyzing expenses overspent

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.

Analyzing expenses overspent

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:

Analyzing expenses overspent

Average Employee expense versus 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:

Digging deeper into the data

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:

Digging deeper into the data

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:

Digging deeper into the data

Employees that exceed 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 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.

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

Analyzing expenses overspent

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.

Analyzing expenses overspent

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:

Analyzing expenses overspent

Average Employee expense versus 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:

Digging deeper into the data

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:

Digging deeper into the data

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:

Digging deeper into the data

Employees that exceed 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 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.

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

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:

Digging deeper into the data

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:

Digging deeper into the data

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:

Digging deeper into the data

Employees that exceed 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 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.

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

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

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

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:

Creating an overview

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

Sharing our analysis

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:

Sharing our analysis

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

Finishing the story

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.

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

Finishing the story

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 5, Authoring 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.

Developing the application

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.

Expenses

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:

PerDiemRates

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:

Airfare

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:

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

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.

Expenses

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:

PerDiemRates

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:

Airfare

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:

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

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.

Expenses

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:

PerDiemRates

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:

Airfare

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:

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

PerDiemRates

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

PerDiemRates

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:

Airfare

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:

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

Airfare

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

Airfare

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:

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

Department

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

Department

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

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:

Budget

The Budget table

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

LinkTable

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

LinkTable

LinkTable

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

Dimensions

Now, let's turn our attention to what has been exposed in 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

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

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

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application. 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:

Measure

Calculation

% of Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount)

/ sum({<[Expense Category] = {'Travel'}>} Budget), '#,##0%')

% of Budget 2

num((Sum(Budget)/sum(Amount))-1, '#,##0%')Amount])

Actual

Num(Sum(Amount),'$#,##0')

Actual - Food

num(sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Actual - Travel

num(sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

Actual/Budget Difference

Num(Sum(Budget)-Sum(Amount),'$#,##0')

Airfare Not Booked in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Average Employee Airfare

Avg({<[Expense Type]={'Airfare'}>} Amount)

Average Employee Daily Hotel Cost

Sum({<[Expense Type]={'Hotel'}>} Amount)/Count(DISTINCT Employee)

Average Employee Daily Meal Cost

Sum({<[Expense Type]={'Breakfast', 'Lunch', 'Dinner'}>} Amount)/Count(DISTINCT Employee)

Average US Airfare (External Source)

Avg({<Trade={'Average Fare'}>} AirfareValue)

Average US Lodging Rates (External Source)

Avg({<Rate={'Lodging'}>} PerDiemValue)

Average US Meal Rates (External Source)

Avg({<Rate={'M&IE'}>} PerDiemValue)

Avg Airfare - Largest Carrier (External Source)

Avg({<Trade={'Average Fare - Largest Carrier'}>} AirfareValue)

Avg Airfare - Low Fare Carrier (External Source)

Avg({<Trade={'Average Fare - Low Fare Carrier'}>} AirfareValue)

Booked Airfare in Advance

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount),'$#,##0.00')

Booked Difference

Num(Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)-Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount),'$#,##0.00')

Booked Difference %

Num((Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'Yes'}>} Amount)/Avg({<[Expense Type]={'Airfare'}, Booked14DaysInAdvance={'No'}>} Amount))-1,'#,##0.00%')

Budget

Num(Sum(Budget),'$#,##0')

Budget - Food

num(sum({<[Expense Category] = {'Food'}>} Budget), '$#,##0')

Budget - Travel

num(sum({<[Expense Category] = {'Travel'}>} Budget), '$#,##0')

Food Difference

num(sum({<[Expense Category] = {'Food'}>} Budget)-sum({<[Expense Category] = {'Food'}>} Amount), '$#,##0')

Travel Difference

num(sum({<[Expense Category] = {'Travel'}>} Budget)-sum({<[Expense Category] = {'Travel'}>} Amount), '$#,##0')

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.

Visualizations

The Travel Expense visualizations

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.

Visualizations

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.