Lab Practice

Open the file you submitted on Monday for Lab Assignment

Part 1: Dimension Group

• For the Line Chart Order Trend add the Cyclic Dimension Group that includes: Year, Quarter and Month. Call this group TIME.
  To create a dimension group: on the Dimensions Tab, select Edit Groups, then New. In Group Name type TIME and select Year, Month and Quarter as your Used Fields Box. Select Cyclic Group. Click OK twice to return to the Dimension Tab. Select Group TIME as your Dimension. Use expression: sum(LineSalesAmount). Observe the result and analyze Order Trend by Month, Year and Quarter

Part 2: Creating a Linear Gauge Chart

• Description:Suppose the CEO (Chief Executive Officer) needs to monitor the Margin% for the Current Year to Date (CYTD). You will create a Linear Gauge chart that displays Margin% based on the selection status. The chart must display the Margin%, include segments for the TARGET Margin%, and show which values fall above or below the TARGET. Gauge charts are used to display the values of a single expression without dimensions.
  The TARGET values are:
  • Poor Performance: Lower Bound = up to 10%
  • Average Performance: Mid Bound = up to 15%
  • Good Performance: Upper bound = over 15%
• Files with detailed instructions are posted in Shared Files and sent by e-mail. Follow instructions to create a chart
  To avoid errors, copy Text in Chart Expression below:
  =Num(Sum(Margin)/Sum(LineSalesAmount), '##.##%')

Part 3: Creating KPI Text Boxes

• KPI - Key Performance Indicators from Investopedia
• Sales KPI Examples from Klipfolio
• We will create three dynamic Text Objects showing Sales, Margin%, and Number of Orders
• To avoid errors, copy the expressions below for each text object:
  • Sales text object: ='Sales: ' & Num(Sum(LineSalesAmount), '$#,##0')
  • Margin% text object: ='Margin%: ' & Num(Sum(Margin)/Sum(LineSalesAmount),'##.##%')
  • Number of Orders text object: ='Orders ' & Num(Count(Distinct OrderID), '#,##0')

Part 4

Dimensions and Measures

• Dimension: It is a descriptive field in the data set which represents few distinct values. Examples - Month, Year, Product ID etc.
• Measures: It is a numeric field on which some calculations are performed for each distinct value of dimension.

• Qlik Design Blog Dimensions and Measures

Practice

• Compare Discount and Number of Orders per Customer (Company Name)
  • Chart: Scatter Chart
  • Dimension: Company Name
  • Measures: X axis - Discount, Y axis - Order ID
• Display Sum and Avg of FreightWeight per Customer (Company Name)
  • Option 1: Create TWO bar charts, Dimension - Company Name, Measure (Expression): Sum(FreightWeight) in first chart and avg(FreightWeight) in second chart
  • Option 2: Create ONE bar chart with Dimension - Company Name, and TWO Measures (Expressions): Sum(FreightWeight) and avg(FreightWeight). On Axes Tab, select avg(FreightWeight) and Position Right(Top)
  • Option 3: Create ONE bar chart with Dimension - Company Name, and TWO Measures (Expressions): Sum(FreightWeight) and avg(FreightWeight) and GROUP both expressions into Expression group: on Expression Tab, select second Expression and then select Group
• Create Dimension Group - Year, Month, Quarter and display Sum of FreightWeight (See Part I for instructions on how to create Dimension Group)
  • Bar Chart
  • Dimension Group that includes Year, Month, Quarter
  • Expression: Sum(FreightWeight)
• To the chart above add additional expression avg(FreightWeight) and create Expression Group to display Sum and Avg of FreightWeight
• Add ShipperID and SupplierID list boxes. Analyze FreightWeight per Supplier/Shipper. Identify companies that works with specific Supplier/Shipper. Use bar chart you created earlier.
• Add CategoryName/Country/City list boxes. Create chart/charts to analyze sales in different categories per Country/City.
  • Possible dimensions: CategoryName/Country/City
  • Expression: sum(LineSalesAmount)
  • Option 1: bar chart with CategoryName as Dimension and Expression: sum(LineSalesAmount) and make selection in County/City list boxes to perform analysis
  • Option 2: bar chart with two dimensions: Country and CategoryName (pay attention on order of the dimensions) and Expression: sum(LineSalesAmount), style: stacked, sorted, and make selections in City list box. Try to change the order of the dimensions and observe the result.
  • Option 3: Pie or Funnel or Block Chart: Dimension: CategoryName and Expression: sum(LineSalesAmount) and make selection in County/City list boxes to perform analysi