Skip to main content

The first sheet: Dashboard

Right-click Dashboard, and then select Open and edit to open the first sheet. The sheet is empty, but it will not be for long. The assets panel to the left contains available charts and fields. Click Sheet (Charts), so that you can start adding charts to the sheet. The properties panel is on the right.

Note: The assets panel and the properties panel have a show/hide functionality. Click open right panel in the bottom right to switch between showing and hiding the properties panel.

Creating visualizations

The purpose of a dashboard is to give a quick overview of the current state of affairs. The focus is on sales trends and figures. The dashboard is not primarily designed for data exploration, but it is of course possible to make selections and analyze the results.

The screenshot for the sheet Dashboard shows the sheet when editing. You drag the charts to the center of the area where you want to place them.

To the left there are two filter panes, the time filter pane without a title and Region. You will begin with these.

Dashboard sheet when editing
Dashboard sheet when editing

Adding the filter panes

The purpose of the filter panes is to filter out a limited data set, which you can analyze and explore.

Do the following:

  1. Drag a filter pane to the sheet.

  2. In the assets panel to the left, click open right panel to open Fields.

  3. Click Date in the list to expand it.

  4. Drag the field Year from to the center of the filter pane, then click Arrow right in the properties panel on the right-hand side to expand the dimension and change its Title to Year.
  5. Drag the field Quarter to the filter pane, then click Arrow right in the properties panel on the right-hand side to expand the dimension and change its Title to Quarter.
  6. Drag the field Month to the filter pane, then click Arrow right in the properties panel on the right-hand side to expand the dimension and change its Title to Month.
  7. Drag the field Week to the filter pane, then click Arrow right in the properties panel on the right-hand side to expand the dimension and change its Title to Week.
  8. Use the handles to resize the filter pane according to the screenshot.

  9. Right-click the filter pane and select Add to master items.

  10. Type the name Period and click Add.

You have created a filter pane and saved it as a master item which facilitates reuse.

The second filter pane only contains one dimension, Region.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts and drag a filter pane to the sheet.

  2. Click Add dimension and scroll down and select the field Region.

  3. Use the handles to resize the filter pane according to the screenshot.

  4. Right-click the filter pane and select Add to master items.

  5. Type the name Region and click Add.

The two filter panes are complete.

Adding the pie chart

Next we will add a pie chart.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts and drag a pie chart to the sheet.

  2. Click Add dimension and add the field Region.

  3. In the assets panel to the left, click open right panel to open Fields.

  4. Locate the field Sales, right-click it and select Create measure.

  5. In the Create new measure dialog, in the Expression box, add Sum in front of (Sales) to create the measure Sum(Sales).

  6. Click Create.

    The measure is added as a master item.

  7. Drag the new measure Sales to the center of the pie chart.

  8. In the properties panel to the right, click Appearance > Presentation and select Donut.

  9. Still in the properties panel, click Colors and legend.

  10. Set Colors to Custom and select By measure in the list.

  11. At the top of the visualization, add the title Sales per Region.

  12. Use the handles to resize the pie chart according to the screenshot.

The donut pie chart is complete. The colors in the pie chart are by measure, which means the higher the value, the darker the color.

You have many options when it comes to coloring the values. Just remember that the colors should serve a purpose and not be used just to make the visualization more colorful.

Adding the bar chart

The next visualization is a bar chart with the top five customers.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts.

  2. Drag a bar chart to the sheet. Place it under the pie chart.
  3. Click Add dimension and scroll down and select the field Customer.

  4. Click Add measure and under Measures, select Sales.

  5. In the properties panel to the right, under Appearance > Presentation, select Horizontal.

    The bars are displayed horizontally.

  6. In the properties panel under Data, click Customer to open the dimension.

  7. In the list Limitation, select Fixed number.

  8. The default setting is to display the top 10. Change the number to 5.

  9. Clear the selection Show others.

  10. In the properties panel, click Appearance > Presentation and set Value labels to Auto.
  11. In the properties panel, click Appearance > Y-axis: Customer.

  12. Under Labels and title, select Labels only.

  13. Click X-axis: Sales.

  14. Under Labels and title, select Labels only.

  15. At the top of the visualization, add the title Top 5 Customers.

  16. Resize the bar chart according to the screenshot.

The bar chart is complete. You have created a bar chart showing the top five customers. When you make selections in other visualizations, these customers will change, accordingly.

If you had not cleared the selection Show others, the fifth bar would have been gray, summarizing all the sales values where the company name is missing. This value can be useful to get an understanding of how much of the sales that cannot be referred to a specific company.

Adding the combo chart

The combo chart combines a bar chart and a line chart and is especially useful when you want to combine values that are normally hard to combine because of their different scales.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts.

  2. Drag a combo chart to the sheet. Place it under the bar chart.
  3. In the assets panel to the left, click open right panel to open Fields.

  4. Click Date.

  5. Drag the field YearMonth to the combo chart and click Add at the top.
  6. Click Add measure and under Measures, select Sales.

  7. In the assets panel to the left, click Linked Object to open Master items.

  8. Under Measures, click Create new.

  9. Copy and paste the following string into the Expression box: 

    (Sum(Sales) - Sum(Cost)) / Sum(Sales)

  10. Type the name Margin Percent and click Create.

    The new measure is added to the list of master item measures.

  11. Drag the measure Margin Percent to the combo chart.

  12. Select Add Margin Percent > As line.

  13. At the top of the visualization, add the title Sales Trend.

  14. Resize the combo chart according to the screenshot.

The combo chart is complete. The two measures Sales and Margin Percent have one axis each, which enables the combination of two totally different scales.

The primary axis to the left is used for Sales and the secondary axis to the right is used for Margin Percent.

Adding the KPI

The KPI visualization can show one or two measure values, and is used to track performance. Color coding and symbols indicate how the figures relate to the expected results.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts.

  2. Drag a KPI chart to the sheet. Place it to the right of the pie chart.
  3. Click Add measure and under Measures, select Sales.

    The sum of sales is added to the KPI.

  4. In the properties panel to the right under Apperance > Color, set Conditional colors to On and click Add limit.

  5. Click the left part of the Value bar, select Color red and Symbol Arrow down in the dialog.

  6. Click the right part of the Value bar, select Color green and Symbol Arrow up in the dialog.
  7. Drag the value limit to the right, to display the sales value as red in the KPI.
  8. In the assets panel to the left, click Linked Object to open Master items.

  9. Click Measures.
  10. Click Create new and type Sum(Margin) in the Expression box.
  11. Type the name Margin and click Create.

    Margin is added to the master item measures.

  12. In the properties panel, under Data > Measures, click Add and select Margin.

    The sum of margin is added to the KPI.

  13. In the properties panel to the right, under Apperance > Color, click Second.

  14. Set the limit for Margin as you did for Sales, but this time drag the value limit to the left, to display the sales value as green in the KPI.
  15. In the properties panel, under Appearance > Presentation select Left in the Alignment drop-down.
  16. In the properties panel, under Appearance > Presentation set Show title to On.
  17. Type the following into the Title text box: 

    Total Sales and Margin

  18. Resize the KPI object according to the screenshot.

If you are using Qlik Sense Desktop, click Save.

The KPI is complete and displays that total sales is below expectations, but still there is a sufficient margin.

The different colors and symbols support the interpretation of the value. Red is worrying, whereas green is good.

Adding the gauge

The gauge is used to visualize a single measure. Just like with the text & image chart, you do not make any selections in the gauge.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts.
  2. Drag a gauge chart to the sheet and place it to the right of the KPI visualization.

  3. Click Add measure.

  4. Select the measure Margin Percent.

  5. In the properties panel to the right, under Data > Measures > Margin Percent > Number formatting, select Number, and in the Formatting list that is displayed, select 12%

  6. Under Appearance > Presentation, select Bar to present the gauge as a bar.

  7. Set Orientation to Custom and select Horizontal.

  8. In the Range limits just above, set Min to -0.5 and Max to 0.5.

  9. Still under Presentation, select Use segments.

  10. Click Add limit.

  11. In the text box that is displayed, enter 0.12, which sets the limit between the left and right segment to 12%.

  12. Press Enter.

  13. Click the left segment and select the red color.

  14. Click the right segment and select the green color.

  15. At the very bottom of the properties panel, open Measure axis.

  16. In Labels and title, select Labels only.

  17. At the top of the visualization, add the title Profit Margin.

  18. Resize the gauge according to the screenshot.

The gauge is complete and displays a large profit margin.

The different gauge colors support the interpretation of the value. Red is worrying, whereas green is good.

Adding the line chart

The line chart will be used to show the quarterly sales trend for the years 2012-2014. The figures for 2014 are for the first half of the year.

Do the following:

  1. In the assets panel to the left, click Sheet to open Charts.
  2. Drag a line chart to the sheet.
  3. In the assets panel to the left, click open right panel to open Fields.

  4. Click Date.

  5. Drag the field Quarter to the line chart and click Add at the top.
  6. Click Arrow right in the properties panel on the right-hand side to expand the dimension Date.Quarter and change its Label to Quarter.
  7. Click Add measure and select Sales from the list Measures.
  8. In the assets panel to the left, click open right panel to open Fields.
  9. Click Date.

  10. Drag the field Year to the line chart and click Add at the top.
  11. Click Arrow right in the properties panel on the right-hand side to expand the dimension Date.Year and change its Label to Year.
  12. In the properties panel, click Appearance > Presentation and select the checkbox Show data points.

  13. At the top of the visualization, add the title Quarterly Trend.

You have completed the first sheet. In the top right corner, click Next to move to the sheet Product Details.