Lab Practice IF Function and Alternate States

• Syntax: if(condition , then , else)

• The if function takes the following three parameters:
  • Condition: An expression that, when evaluated, results in either true or false. Relational and logical operators are used to create the expression.

  • If Condition is TRUE, Then will be performed

    if Condition is False, Else will be performed

• A nested If expression can be constructed in the following manner:
  If(condition1, expr1,
  If(condition2, expr2, expr3))

• Heat Charts: A heat chart is a cell matrix in which each individual value is color-coded based on a threshold.

• Design Pivot Table with Dimension: Carrier Name, Year, and Month

• Expression: $(eLoadFactor) and type Load Factor % as the expression Label

• To define a background color from the expression's attributes click on the small plus sign located next to the expression name in the Expressions dialog window. Highlight the Background Color attribute.

• Once the attribute is highlighted, go to the Definition pane to the right and bring up the Edit Expression window.

• The expression we will define for this attribute is as follows:
  If([Load Factor %] >= 0.85, Green(),
  If([Load Factor %] >= 0.70, LightBlue(),
  LightRed()))

• Click on OK to close the Edit Expression dialog

• Highlight the Text Color attribute for the Load Factor % expression and, in the Definition pane, type White(). This will ensure that the cell text uses a white color to make it more readable within the three different background colors we previously defined.

• Number format: Fixed to 2 decimal, Show in percent (%)

• Challenge: instead of static limits (0.85 and 0.7) create TWO variables and input/slider box to allow interactive action

• Colormix wizard
  • Open the properties of the Pivot Table. On Expressions select Background Color attribute to open the Edit Expression window. Clear the expression's current content and, from the File menu, select Colormix Wizard.
  • Step1: Value Expression: $(eLoadFactor)
  • Step2: Disable Auto Normalize, Upper Limit = 1, Color = Green, Intermediate=0.75, Color Blue, Lower Limit=0.5, Color Red
  • Step3: Enhanced Colors enabled and Value Saturation set to Use Upper (Lower) Color
  • Click Finish

• Comparative Analysis using Alternate states

  Suppose we want to compare how the number of international flights arriving at the city of Chicago, IL, and performed by US carriers, compares to the number of domestic flights departing from Chicago and bound to the State of California, performed by US Carriers as well

  • Create New Sheet: Alternate states
  • Go to Settings | Document Properties and activate the General tab from the Document Properties window and click on the Alternate States From the Alternate States window, click on the Add button and enter Group A into the New State Name window. Do the same for Group B.
  • At this point, we have defined two different states: Group A and Group B. Each of these states will be used to save the two different record sets we want to analyze, which will be given by user selections in a set of listboxes we will now create.
  • Create List Boxes: Flight Type, Destination City, Origin City, Destination State Code
  • Create a container object with all of the above-listed listboxes in it. From the General tab of the container object properties window, select Group A in the Alternate State drop-down list
  • Copy the Container and Change Alternate State to Group B
  • Name first container A and the second B
  • Create a Bar Chart, Dimension: Period,
    Expressions: Sum({[Group A] * $} [# Departures Performed])
    label it A
    Sum({[Group B] * $} [# Departures Performed])
    label it B
  • Number format for both expressions: Integer
  • Finish the chart

  To perform comparative analysis: select the following values in each of the alternate states:

  Group A:
  • Flight Type: International, US Carriers Only
  • Destination City: Chicago, IL
  Group B:
  • Flight Type: Domestic, US Carriers Only
  • Origin City: Chicago, IL
  • Destination State Code: CA
• Select YEAR 2011 (on one of the other sheets you should have YEAR listbox). Analyze the result.