These labs are based on Garcia, M., Harmsen, B. (2012). QlikView 11
for Developers
TemplateChapter2AirlineOperations QilkView File
In this lab you will work with real publicly available data.
The original data files have been downloaded from The Bureau of
Transportation
Statistics of the United States website (http://transtats.bts.gov), and
have been
pre-processed.
Create the following List Boxes: Carrier Name, Origin City, Origin
Country, Origin State, Destination City, Destination Country, and
Destination
State
Answer the following questions. Create a textbox to document your
answers:
Which carriers have logged flights departing from Accra, Ghana?
In which
cities and states are those flights arriving?
Create a new Sheet; Dashboard. Add the following List Boxes:
Year, Quarter, Month, Carrier's
Operating
Region, Carrier Group, Aircraft Group, and Flight Type.
Modify the List Box for Year to display all 3 years in one row
Modify the List Box for Month, to display a table of 2 rows and 6
columns
Modify the List Box for for Quarter to display a table of 2 rows and 2
columns
Create a Bar chart to display the number of departed
flights per year.
Dimension: Year, Expression: Sum ([# Departures
Performed])
Sort in Descending Order
Choose Integer for Number Format
Modify the chart: Create a Cyclic Dimension Group: Year/Month
Find the busiest year and busiest month
Name one Country which didn't have any departures in 2010
Name ony City which didn't have any departures in July
Modiy the Chart to Create a Cyclic Expression Group. Add the following
Expressions:
Sum ([# Transported Passengers]). Set the label for this
expression
as # of Enplaned Passengers.
Sum ([# Transported Freight]). Set the label for this expression
as
Transported Freight.
Sum ([# Transported Mail]). Set the label for this expression
as
Transported Mail.
Format ALL expressions as Integers
Find the busiest month/year for each category (expression).
Clone this chart and convert it to Line Chart
Make the following modifications: Clear Sort Option
Add TrendLine AVERAGE for each expression
Analyze the Chart
Create a new Sheet: MoreCharts
Create Straight Table, call it Top 10
Routes, that displays the top
routes in terms of
number of flights, enplaned passengers, transported freight, and
transported mail.
Dimensions: From - To City
Expressions and their corresponding labels:
Flights: Sum ([# Departures Performed])
Passengers: Sum ([# Transported Passengers])
Freight: Sum ([# Transported Freight])
Mail: Sum ([# Transported Mail])
Select No Totals for
all four
of them.
On Presentation Tab,
enable the Max Number (1 - 100) checkbox and set it to 10.
Format
all expressions to the Integer format.
Add Expression into List Box:
Carrier Name List Box expression: sum([#
Departures Performed]).
Change the name of the List Box to: Carrier Name
(#Departures)
Create a Multi Box
Title: Flight Information.
Choose the following Fields: From - To City,
Origin City, Origin Country, Destination City, Destination Country, and
Distance Interval.
On the Sort tab, Distance Interval,
in the Sort by section, uncheck the Text option, and mark the Load
Order checkbox.
Find Destination Cities and Countries, Origin Cities and Countries
and Carrier Names that fly Distance Interval 9500-9999
Create the Current Selestions Box
Wait for Demonstration of the Features
Create a Bookmark Object
Wait for Demonstration of the Features
Create a New Sheet: AdvancedBarChart
Create a bar Chart to display number of passengers
and number of available seats by flight type.
Title: #passengers/#available seats by flight type
Dimension: Flight Type
Expressions: Sum ([# Transported Passengers]), Sum([# Available
Seats]). Label them: [# Transported Passengers and # Available
Seats
For each Expression: Select Values on Data Points checkbox
On Axes Tab: Select Show Grid for BOTH Axes
On Numbers Tab: Select Fixed to 1 Decimals, and Million for Million
Symbol
Analyze the Chart
Create Pie Chart to display Market Share.
Dimension: Carrier Name,
Expression: Sum([# Transported Passengers]). Select Relative and
Show Values on Data Points. Click
finish. The chart
looks too crowded.
Open chart properties and go to the Dimension Limits
tab.
Mark the Restrict which values are displayed using the first
expression
checkbox.
Select the Show only values that accumulate to radio button and set
the corresponding value to 50% relative to the total. Enable the Include
Boundary Values checkbox as well.
In this part you will analyze the
relationship between the number of passengers, number of transported
mail, and the
number of performed departures at the carrier level. To visualize this
create a
scatter chart.
Dimension: Carrier Name
Expression X: # Transported Mail, Expression Y: #
Transported Passengers.
Select the Bubble Chart and enter the
following in the Bubble Size
Expression input field:
Sum([# Departures Performed])