Lab Practice - Variables, Advanced Search, Dynamic Bookmarks

• In this example we will analyze FTE: Full-Time Equivalent Employees per Aircraft. Employees per aircraft provide one measure of an airline's efficiency with respect to the average number of all its employees per unit of production. The smaller the number of employees per aircraft indicates greater efficiency. Note: Full-time Equivalent Employee (FTE) calculations count part-time workers as one-half of a full-time employee.
• In Variable Overview look on TWO variables: vTotalFTEs that has value =Sum([# Equivalent FTEs]) and eFTEs that has value Sum([# Equivalent FTEs])
• IMPORTANT: the equal sign in variable definition tells QlikView to calculate the variable across all dimensions regardless of the context in which the variable is used.
• Create straight table. Use Carrier Name as the only dimension and the following expressions:
  • $(eFTEs), Label FTE, Format Number to Integer
  • $(vTotalFTEs), Label TOTAL FTE, Format Number to Integer
  • $(eFTEs) / $(vTotalFTEs), Label ratio, Format Number to two-decimal format and specify it to be shown as percentage.
• Another way to achieve showing the ratio is enabling the Relative checkbox in the Expressions tab or using the TOTAL qualifier. When TOTAL is added to aggregation functions it disregards chart dimensions and make the calculation over the entire record set as defined by the current selection state.
• Create straight table. Use Carrier Name as the only dimension and the following expression $(eFTEs) and select elative checkbox in the Expressions tab. Observe the result
• Create straight table. Use Carrier Name as the only dimension and the following expression Sum([# Equivalent FTEs]) / Sum(TOTAL [# Equivalent FTEs])
• Add the following expressions to the Straight Table:
  Sum([# Departures Performed]) / Sum(TOTAL [# Departures Performed]) as two-decimal format and specify it to be shown as percentage.
  Sum([# Departures Performed]) as Integer
  Sum(TOTAL [# Departures Performed]) as Integer
  Observe the result and notice that the Sum([# Departures Performed]) / Sum(TOTAL [# Departures Performed]) returns the percentage of flights for a particular carrier, month, and year relative to the total flights of all carriers, and for all years and months available in the current selection state.
• Create a Straight Table to count how many interstate routes each carrier covers. Dimension: Carrier Name, Expression: count(Distinct [From - To State])
• We would like to classify carriers by the number of interstate routes they serve. Question to answer: how many carriers would fall under each category?

  Implementation:

  • Straight Table
  • from the Dimensions dialog window, click on the Add Calculated Dimension...button
  • Use the following expression for Calculated Dimension: Aggr(Count(DISTINCT [From - To State Code]), [Carrier Name])
  • Use the following expression for Expression field: Count(DISTINCT [Carrier Name]), label it # of Carriers
  • Observe the chart
• Suppose we want to use a text object to present the maximum, minimum, and average number of interstate routes served by all carriers;
  • Create a variable eRoutes with the following value Count(DISTINCT [From - To State Code])
  • Create a text object with the following text: ='Max Value: ' & Max(Aggr($(eRoutes), [Carrier Name])) & Chr(10) & 'Min Value: ' & Min(Aggr($(eRoutes), [Carrier Name])) & Chr(10) & 'Avg Value: ' & Avg(Aggr($(eRoutes), [Carrier Name]))
• Advanced Search Expressions:
  • Create New Sheet: Search
  • Create Current Selection Box
  • Create Bookmark Box
  • Create a multibox with the following fields: Origin Country, Origin City, Destination Country, Destination City
  • Suppose you would like to to keep track of destination cities based on the following criteria:
    Cities that have been destination to flights originated outside the US
    Only cities within the US
    Their global load factor needs to be 80% or more
  • Right-click anywhere on the list and select Advanced Search.
  • In the Search Expression input box, enter the following expression: =[Origin Country] <>'United States' and [Destination Country] = 'United States' and $(eLoadFactor) >= 0.8
  • Click on Go to apply the selection and then click on Close to close the Advanced Search dialog.
  • Open the Destination City drop-down list, observce the list of cities that have an occupancy of 80% or more for incoming international flights. Observe Current Selections box.
  • Create List Box with field Carrier Name
  • We would like to create a bookmark which will always select the top ten carriers by number of enplaned passengers
  • Clear all selections.
  • Right-click on the Carrier Name list box and select Advanced Search.
  • In the Search Expression input box enter the following code: =Rank($(eEnplanedPassengers)) <= 10.
  • Click on Go to perform the search, check that this changes the selection in Carrier Name.
  • Click on Close to close the Advanced Search dialog.
  • Select Bookmarks | Add Bookmark from the menu.
  • Enter Top 10 carriers by transported passengers as the Bookmark Name.
  • Check the Make bookmark apply on top of current selection checkbox.
  • Click on OK to save the bookmark.
  • We now have a bookmark that, based on the current selection, will always select the top ten carriers based on the number of transported passengers.