Lab Practice - Set Analysis

• General Syntax Example:
  Sum({set expression} [# Departures Performed])

• Two main types: start with $ sign and start with 1

• dollar sign - alternative record set will be initially based on the current selections

• the number 1 - the full record set of all the data contained in the document, ignoring all user selections

• Detailed Syntax Example:
  Sum({$ < Field1 = {NewValue1}, Field2 = {NewValue2} >} [#Departures Performed])
  • FieldName = {value} for numeric value
  • FieldName = {'TextValue'} for text (string) value
  • FieldName = {"SearchString"}: if we want to use a search string as the value definition

• Examples: in ALL examples we will create BAR CHART with Carrier Name as Dimension and expressions listed below. Sort the chart in decreasing order and display 10 largest values.
  • Expression: Sum({$<[Carrier Group] = {'Foreign Carriers'}>}[# Departures Performed])
    Action: Total number of flights performed, but only taking into account the record set defined by the current selections ($), and where the Carrier Group field has a value of Foreign Carriers. All other Carrier Groups are excluded. In this example, if the user has specifically selected a different value in the Carrier Group field, that selection will be IGNORED

  • Expression: Sum({$< Year = {2010}>} [# Departures Performed])

  • Expression: Sum({$< Year = {"20*"}>} [# Departures Performed])
    Action: All years that begin with 20 will be taken into account.

  • Expression: Sum({$< Year = {">=2010"}>} [# Departures Performed])
    Action: Year is greater than or equal to 2010

  • Expression: Sum({$<[Carrier's Operating Region] = {'Domestic', 'Latin America'}, Year = {2010}>} [# Departures Performed])
    Action: Carrier's Operating Region field is either Domestic or Latin America, and only those records corresponding to the year 2010.

  • Expression: Sum({1<[Carrier's Operating Region] = {'Domestic', 'Latin America'}, Year = {2011}>} [# Departures Performed])
    Action: The calculation will use the entire document record set as a starting point, disregarding all user selections, but take into account only those records where the Carrier's Operating Region is either Domestic or Latin America, and will only look at those records corresponding to the year 2011.

  • Using Variables with Set Analysis
    • Create the following variables with the corresponding initial values:
      vInterestGroup = 'Foreign Carriers'
      vYear = 2010
      vRegion = 'International'
      vMonth = 'Jul'

    • Expression: Sum({$<[Carrier Group] = {$(vInterestGroup)}>}[# Departures Performed])

    • Expression: Sum({1<[Carrier Group] = {$(vInterestGroup)}>}[# Departures Performed])

    • Expression: Sum({$<[Carrier's Operating Region] = {$(vRegion)}>} [# Departures Performed])

    • Create an Input Box for each variable. Make sure text variables are enclosed in single-quotes.

    • To compare to consecutive years, we will use the following expressions:
      Sum({$ < Year = {$(=Max(Year))}>} [# Departures Performed])
      Sum({$ < Year = {$(=Max(Year)-1)}>} [# Departures Performed])

    • Graded Lab Practice
      • Create Straight Table, Dimension: Carrier Name, Expression: sum([# Departures Performed])/sum(TOTAL [# Departures Performed])
      • Create gauge and mini chart (dimension Month) for the same expression
      • Use the following expression for the MAX gauge value:
        =Max(Aggr(sum([# Departures Performed])/sum(TOTAL [# Departures Performed]), [Carrier Name]))
      • Use variables for gauge segment2 Lower Bound
      • Create slider for the gauge variable
      • Create a Line Chart, with Dimension Month and the same expression as in Mini Chart. Compare results. Look on 15 largest carriers, and describe the trend in departures by month. Any exceptions?
      • Add list box: Origin Airport Code. Analyse # of performed departures in PHL - Philadelphia International Airport by different carrier groups.