The Formula Picker allows you to 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.
YTD
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:
QTD
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:
MTD
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:
WTD
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:
Last
The Last selection formula selects previous periods, counting backward from the Selections date.
With the Formula tab selected:
- Select your Granularity (for example, Weeks or Dates) and choose the value you want to use as your reference.
- Define your Last function (pick Last > Number of periods > Period from the Selection Formula):
- When Granularity and Period match, the formula simply means “last X periods.” For example, choosing Weeks with "Last 3 Weeks" selects the three weeks up to and including the selected date.
- When the Granularity uses smaller units than the Period, the formula sets the length of the selection period, and the granularity sets the size of each selection within that. For example, the Dates granularity plus "Last 3 Weeks" selects the last day from each of the three Weeks (see Example 2 below).
Once selected, the date is shown in the Selections field at the top of the dialog. It is this date that your selection formula will count backwards from.
You cannot select a Period with smaller units than your Granularity selection.
Example 1: Last Seven Days
The following example filters the query by 7 days prior to the reference date of 25 January, 2008. Since the Granularity (green arrow below) and Period (purple arrow) match, the selection formula describes the selection and this configuration selects the date range from 19-01-2008 to 25-01-2008:
Example 2: One Day Per Week, Last Three Weeks
The following example also includes dates prior to the reference date of 25 January, 2008:
- Granularity indicates that the selections are individual Dates (green arrow below), one of which is the reference date.
- The Selection Formula "Last 3 Weeks" (purple arrow), then indicates that the dates should be selected from the three week window prior to that.
As a result, the selected dates are 25‑01‑2008, 18‑01‑2008, and 11‑01‑2008 (orange highlight). For the period defined by the Selection Formula, the date that corresponds to the configured granularity is chosen.
Next
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.
- Select the Granularity.
- Select the Next function.
- Specify the number of periods to select.
- 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:
Lead
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.
- Select the Granularity.
- Select the Lead function.
- Specify the number of periods later than the selected date.
- 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:
Lag
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.
- Select the Granularity.
- Select the Lag function.
- Specify the number of periods earlier than the selected date.
- 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:
