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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset