Lab Practice
Open the file you submitted 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.
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