Date Calendar

The Date Calendar allows your readers to select options from date-key columns. They can filter their discoveries or presentations by selecting date "slices"; that is, particular dates, ranges of dates, or formulas based on dates (such as, year to date or last x days to date).

Note: You may also be able to slice by dates using drop-down lists or buttons (say), where your dates are not modeled as date-key columns. The Date Calendar is only available for date-keys.

You can show or hide any of these pickers from the slicer's settings dialog.

Simple Picker

The Simple Picker allows you to select the items that should be used to filter the query. To select more than one element, enable Multi Select (purple arrow below). With multi select enabled, you can select multiple elements from any level of granularity (orange highlight).

The example below filters the query by the four dates selected: 5, 6, 12, and 13 January, 2008:

Range Picker

Use the range picker to filter the query by a date range, beginning with the From date, up to and including the To date. Select the required level of granularity, then select the start date from the left, and the end date from the right.

The example below filters the query by all dates between 1 April 2009 and 30 September 2009, inclusive:

This is an efficient way to filter the query by a range of dates that doesn't correspond with the granularity. For example, if you want to filter by Q1, you can either select Q1 from the Quarters level in the Simple picker, or you can select the start and end dates for Q1 from the Range picker; the easiest option is the former.

However, if you want to filter by a range of dates within Q1, say, 29 January - 4 March, this is easily achieved using the Range picker.

Formula Picker

Filter the query by a formula based on the selected date element. Start by selecting the granularity level, then select the date element, then choose the required function.

The type of functions available depends on the selected level of granularity:

In this dialog, you are filtering the query by a functional selection by choosing the required granularity, date-time period, and function. Pyramid then calculates the formula on-the-fly, without producing any calculation elements (such as those created by the Time Calculation wizard).

Functional Selections

The Formula calendar picker supports the functional selections described below. You can also review detailed documentation about each of these functions here.

ClosedYTD

The Year to Date function is available for all granularity levels. It filters the query by all dates or time periods from the beginning of the selected year, up to and including the selected element.

This example will filter the query by all dates in 2009, up to and including 18 August:

Here, the granularity is set to months; the selection will filter by all months in 2009, up to and including August:

ClosedQTD

The Quarter to Date function is available for levels lower than quarters: months, weeks and dates. It filters the query by all dates or time periods from the beginning of the selected quarter, up to and including the selected element.

Here, the selection filters the query by all dates from the beginning of Q2, 2009, up to and including 19 May:

Now the granularity is changed to months; the selection will filter by all months in Q2 2008, up to and including May:

ClosedMTD

The Month to Date function is available for levels lower than months: weeks and dates. It filters the query by all dates or weeks from the beginning of the selected month, up to and including the selected element.

This selection filters the query by all dates from the start of August 2010, up to and including 19 August:

This selection will filter the query by all weeks in 2009, up to and including week 2:

ClosedWTD

The Week to Date function is available for dates. It filters the query by all dates from the beginning of the selected week, up to and including the selected element.

Here, the query will be filtered by all dates from 12 April 2009 (the start of the week where the selection was made) up to and including 16 April:

ClosedLast

The Last function is available for all granularity levels. It filters the query by all dates or periods in the last n time periods, prior to the selected time period.

  1. Select the granularity.
  2. Select the Last function.
  3. Enter the number of periods to go back.
  4. Choose the type of period to go back. The available selections will depend on the granularity that was selected.

The example below filters the query by 7 days prior to 25 January, 2008:

In this example, the query is filtered by the 5 weeks prior to 24 January, 2008:

And here, the query is filtered by the 6 months prior to August, 2008: