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-downs 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, select the Multi Select checkbox (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. (Which functions are available depends on the selected level of granularity.)

The example below filters the query by all dates in 2009 (YTD), up to and including 18 August:

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 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 date.

This example will filter the query by all dates in 2009 (YTD), 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 filters 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. Specify the number of periods to go back.
  4. Choose the units (type of period) to go back by. (The available selections depend on the selected granularity.)

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

In this example, the query is filtered by the 6 months prior to August, 2008:

ClosedNext

The Next function is available for all granularity levels. It filters the query by all dates or periods in the next n time periods, after the selected date. Returns a date range.

  1. Select the Granularity.
  2. Select the Next function.
  3. Specify the number of periods to select.
  4. Choose the units (type of period) to use. (The available selections depend on the selected granularity.)

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

In this example, the query is filtered by the 6 months after August, 2008:

ClosedLead

The Lead function is available for all granularity levels. It filters the query by the single date that is later than the selected date by the specified number of periods.

  1. Select the Granularity.
  2. Select the Lead function.
  3. Specify the number of periods later than the selected date.
  4. Choose the units (type of period). (The available selections depend on the selected granularity.)

The example below filters the query by the date that is 5 weeks after 25 January, 2008:

In this example, the query is filtered by the date that is 6 months after August, 2008:

ClosedLag

The Lag function is available for all granularity levels. It filters the query by the single date that is earlier than the selected date by the specified number of periods.

  1. Select the Granularity.
  2. Select the Lag function.
  3. Specify the number of periods earlier than the selected date.
  4. Choose the units (type of period). (The available selections depend on the selected granularity.)

The example below filters the query by the date that is 7 days earlier than 25 January, 2008:

In this example, the query is filtered by the date that is 6 months earlier than August, 2008:

Function Picker

Filter the query dynamically using a date function that references the "latest date." Which date is the latest, depends on your model:

  • If your model includes dates up to and including the current date, then the latest date is today.
  • If your model includes dates from the past, then the latest date is the latest date that we have data for. If we have data from 2010 only, then the latest date is in 2010.
  • If your model includes future dates, then the latest date is the last of the future dates. If we have data to 2050, then the latest date we have data for is in 2050.

The example below (YTD) filters the query by the year to date. This means that, if the latest date you have data for is today, Pyramid will select the data for this year up to and including today: