416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Using Date Offset Parameters

Hide navigation
RSS
Modified on Wed, 01 Oct 2014 11:06 AM Categorized as Data Grid, Design, Level-Advanced, Parameters
Click to return to: Documentation | Designing Dashboards


Overview

This article shows how to create a chart that compares the same measure over two different time periods. This period over period comparison is achieved using the Date Offset filter type option which is available when creating a dashboard parameter.

Creating a period over period comparison

Initial setup

In this example, assume the availability of a KPI with the following attributes:

  • KPI Name: Downloads By Date
  • Measure: Number of Downloads
  • Dimension: Date (time dimension)
  • Filter: Public, with default range from Jan 1, 2010 to Dec 31, 2011, and monthly grain
  • Preferred visualization: Line Chart

Set up a chart to display this KPI as follows:

  • Drag a line chart from the toolbox to the dashboard canvas.
  • Drag and drop the Downloads By Date KPI onto the line chart.
  • Drag a legend from the toolbox to the canvas.
  • Set the chart's Legend property to point to the legend.
  • Save, and then preview the dashboard.

Line chart showing the Downloads By Date KPI over a period of two years (2010 and 2011).

Line chart showing the Downloads By Date KPI over a period of two years (2010 and 2011).


Create the comparison line

Create the comparison line (the one that we will offset) simply by dragging the same Downloads By Date KPI onto the existing line chart. It will be given the KPI descriptor name, Downloads By Date (2).

Line chart showing the two KPI instances overlapping.

Line chart showing the two KPI instances overlapping.


Add a filter parameter

Follow these steps to add a filter parameter and connect it to the filters of both KPI instances:

  1. Click Parameters in the sidebar.
  2. Click Add New Parameter. The Set Up a Dashboard Parameter wizard is displayed.
  3. In the first step of the wizard, enter a name for the parameter and choose Filter as the filter type. Click Next.
    Create filter parameter step 1.
  4. In the second step of the wizard, choose both KPI instances for the parameter to act upon and then select their corresponding filters in the Filter grid below. Click Next.
    Create filter parameter step 2.
  5. In the last step of the wizard, select Calendar (Range) as the dashboard control type.
    Create filter parameter step 3.
  6. Click Finish.

The newly created filter parameter appears in the Parameters toolbox.

Add a date offset parameter

Follow these steps to offset the second KPI instance by 1 year, which allows for a comparison of the measure between the 2010 time period and the 2011 time period:

  1. Click Parameters in the sidebar.
  2. Click Add New Parameter. The Set Up a Dashboard Parameter wizard is displayed.
  3. In the first step of the wizard, enter a name for the parameter and choose Date Offset as the filter type. Click Next.
    Create parameter step 1.
  4. In the second step of the wizard, choose the KPI that the parameter will act upon by selecting the checkbox for the Downloads By Date (2) KPI. The public filter from this KPI will be automatically listed and selected in the Filter grid below. Click Next.
    Create parameter step 2.
  5. In the last step of the wizard:
    • Choose a direction for the offset (Forwards or Backwards) by using the first dropdown list.
    • Set the number of units to offset using the numeric up-down (cannot be negative).
    • Choose the type of unit for the offset by using the second dropdown list (⪚ Calendar Year).
      Create parameter step 3.
    • Click Finish.

The date offset parameter appears in the Parameters sidebar.

Date offset parameter.

Date offset parameter.


Save and preview dashboard

You can now save and preview the resulting dashboard which gives you a year-over-year comparison (2010 and 2011):

Year-over-year comparison.

Year-over-year comparison.


Note that the chart only displays the x-axis labels for the first KPI (Downloads By Date) that was added, and not the second KPI (which is the one that has been offset). A useful option then is to set up a legend to clearly indicate the time period that each KPI (series) represents.

Examples

Using date offset with a data grid

The following example shows how to use date offset in conjunction with a data grid control.

First, assume the availability of a KPI with the following attributes. (You can use a dataset instead, it works the same.)

  • KPI Name: Downloads By Date
  • Measure: Number of Downloads
  • Instant Dimension: Download Date, which is replaced with the Time dimension
  • Filter: Public, with default range from Jan 1, 2010 to Dec 31, 2011, and monthly grain
  • State Group: State Group 1 with two states:
    • Bad state: From 0 to 200
    • Good state: From 200 to +Infinity

Create a new dashboard:

  1. Drag a Data Grid control from the toolbox to the canvas.
  2. Drag the Downloads By Date KPI and drop it over the data grid. Accept all of the default options in the wizard.
  3. Save, and then preview the dashboard.

Data grid with a state indicator column.

Data grid with a state indicator column.


Next, add the Downloads By Date KPI to the data grid (for the second time). This is so we can display a second Number of Downloads column that will be offset later.

  1. Drag the Downloads By Date KPI from the toolbox to the data grid.
  2. In the Define display options step of the Data Grid Binding Setup wizard, select the Show as column option, and then click Next. (This option lets you add an extra data grid column without losing existing columns.)
    Show as column option.
  3. In the Define the relationship between the data sources step of the wizard, choose either Filter or Alignment mode, and then set the Parent data source value dropdown list to the Time dimension. Click Next.
    Define the relationship.
  4. In the Configure data visualization column step of the wizard, change the Header text to Offset Downloads.
    Configure the data visualization column.
  5. Click Finish.
  6. Save and preview your dashboard. You will see an extra Offset Downloads column with the same values as the original Number of Downloads column. The values are the same because date offset filtering has not been applied yet.

Add a second state indicator column to the data grid that corresponds to the Offset Downloads column. You will need to do this manually as follows:

  1. Use the Columns property of the data grid to add a new State Indicator column.
  2. Set the column's Data Source Type to Secondary.
  3. Set the Data Source to Downloads By Date(2), which is the second KPI instance.
  4. Set the Data Column to Downloads By Date(2).Number of Downloads.
  5. Set the Header Text to Offset State.
  6. Use the States property of the column to add two new states.
  7. Set the first state:
    • End Value: 200
    • Name: Bad
    • Shape Fill: #FFB4391D
  8. Set the second state:
    • End Value: Infinity
    • Name: Good
    • Shape Fill: #FF1A9B22
    • Start Value: 200
  9. Save, and then preview the dashboard.

The data grid appears with two extra columns which have the same values as the original two columns.

Data grid with two extra columns.

Data grid with two extra columns.


Finally, to apply the date offset, follow these steps:

  1. Add a Filter parameter to control the filters of both KPI instances. The steps are the same as described here.
  2. Add a Date Offset parameter to control the filter of the second KPI instance, Downloads By Date(2). The steps are the same as described here, except we will offset just one calendar month backwards.
    Offset 1 month backwards.
  3. Save, and then preview the dashboard.

Here is the resulting dashboard with the two additional columns offset by 1 month.

Data grid with two date offset columns.

Data grid with two date offset columns.


Notes

  • When you apply date offset to an OLAP-based KPI that uses date mapping, note that portions of the offset series may use different x-axis labeling than the original series, which gets its labeling from the virtual cube.

Related topics


Click to return to: Documentation | Designing Dashboards

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.