MicroStrategy ONE

Filtering by offsetting the current date: Dynamic dates

You can create dynamic dates and times when you qualify on a date attribute form with the date datatype. Dynamic dates can be either a fixed set of dates or different date ranges that change through time. For example, a date qualification on the Day attribute can return data only for the days that you have specified in the filter condition. A dynamic date can be used in a report that examines revenue amounts in the previous two months. This is represented as "today" with an offset of two months.

You can express dynamic date qualifications in several ways, as shown in the following examples:

  • An offset of four years, three months, two weeks, and one day from today

  • Monday of this week

  • Monday of this week with an offset of two days

  • The fourth of this month

  • The fourth Wednesday of this month

  • May fourth of next year

  • The third Wednesday in May of this year

While evaluating a dynamic date such as "first of this month minus seven days," the order in which these two parts are calculated is significant. The addition or subtraction of days or months (the offset) is always done first, before "first of this month", "first Sunday of this month", and so on is calculated. For example:

  • If today is February 13th, then "today minus seven days" is February 6th, and "the first of the month of today minus seven days" is February 1st.

  • However, if today is February 2nd, then "today minus seven days" is January 26th, and "the first of the month of today minus seven days" is January 1st.

Dynamic dates are available for any attribute form that has a Date or Datetime format type. For an attribute form with a Datetime format type, dynamic times are also available. Hours, minutes, or both can be calculated dynamically. Hours, minutes, or both can be defined as static. For example, if the current time is 12:40 PM:

  • Then "this hour plus 1 hour" and "this minute minus 0 minutes" (dynamic hour and dynamic minute) is calculated as 1:40 PM.

  • Then "this hour plus 1 hour" and "Static Minute = 24" (dynamic hour and static minute) is calculated as 1:24 PM.

  • Then "Static Hour = 11 AM" and "this minute minus 10" (static hour and dynamic minute) is calculated as 11:30 AM.

  • Then "Static Hour = 11 AM" and "Static Minute = 24" (static hour and static minute) is calculated as 11:24 AM.

When you create a dynamic date for an attribute form with a Datetime format, you must also create a dynamic time.

Dynamic date example

The following report uses two filters, each containing a dynamic date qualification. The first filter, applied as the report filter, is called Today and qualifies on the current date. The second filter, applied as a condition on a metric condition, is called Today - 2 months. It qualifies on the current date minus an offset of two months. The Revenue metric calculates revenue for the current date because the report filter (the Today filter) is applied. The Revenue (2 months ago) metric calculates revenue for today's date minus two months because the metric contains a condition (the Today - 2 months filter).

The following report sample was executed on 9/26/09. The Revenue metric returns revenue for 9/26/09, and the Revenue (2 months ago) metric calculates revenue for 7/26/09. This report allows you to compare the revenue values quickly and easily.

For instructions to re-create this example, see the Advanced Filters chapter of the Advanced Reporting Help.

For an example of basing the dynamic date on a prompt answer, rather than today's date, see Filtering on a dynamic date based on a prompt answer.

Prerequisite

This procedure assumes that you have already begun creating an attribute qualification. If you have not, see Steps to create an attribute qualification.

To define a dynamic date

  1. In the Attribute Qualification pane, select a date attribute, such as Ship Date or Day, in one of the following ways:

    • Type the name of the attribute in the Attribute field and click OK.

    • Click ... (the browse button) to select an attribute from the Attributes folder. Click OK.

    • Drag the attribute from the Object Browser into the Attribute field.

  2. Select ID or Date, depending on which attribute form has either a Date or Datetime format, from the Qualify On drop-down list.

  3. Select the desired operator from the Operator drop-down list.

  4. Click the calendar icon to define the dynamic date. The Date and Time Editor opens.

  5. On the Date tab, select Dynamic date.

  6. Select the View point date(the offset date) by doing one of the following:

    • If you do not want to offset the dynamic date, continue to the next step. For example, if you want the dynamic date to be the Monday of the current week, you do not have to define the offset date; you can just choose a date adjustment.

    • To offset the dynamic date by days, select Plus or Minus from the drop-down list by days. Type the offset value in days, or select the offset value using the up and down arrows.

    • To offset the dynamic date by months, select Plus or Minus from the drop-down list by months. Type the offset value in months, or select the offset value using the up and down arrows.

    • You can offset the dynamic date by both days and months by performing both of the previous actions.

    : You can view the result of the offsets in thePreviewarea.

  7. If you want to adjust the dynamic date, select the Choose date adjustments based on the view pointcheck box. The Weekly, Monthly, and Yearly options are now enabled. Choose one of those options:

    • Weekly: Select the day of the week to use as the adjustment. For example, if today is Wednesday, January 28th, then "today minus seven days" and "Sunday" is calculated as Sunday, January 18th. Without the offset of seven days, "Sunday" is calculated as Sunday, January 25th.

    • Monthly: Select one of the following:

      • To choose a numeric day of the month, select Day. Type the (numeric) day of the month. For example, if today is January 28th, then "today minus seven days" and "Day = 2" is calculated as January 2nd. Without the offset of seven days, "Day = 2" is still calculated as January 2nd.

      • To choose a day of the week, select The. Choose the ordinal number (First, Second, and so on) and the day of the week from the drop-down lists. For example, if today is Wednesday, January 28th, then "today minus seven days" and "the second Sunday" is calculated as Sunday, January 11th. Without the offset of seven days, "the second Sunday" is still calculated as January 11th.

      • To offset from the last day of the month, select Reverse Count. Type the offset value in days, months, or a combination of days and months. For example, if today is January 28th, then "Reverse Count = 1" is calculated as January 31st, the last day of the month, and "Reverse Count = 10" is calculated as January 22nd. "Today minus 10 months" and "Reverse Count = 10" is calculated as March 22nd of the previous year.

    • Yearly: Select one of the following:

      • To choose the numeric day of a specific month, select On. Choose the month from the drop-down list and type the (numeric) day. For example, if today is January 28th, then "today minus 10 months" and "on February 1" is calculated as February 1st of the previous year. Without the offset of 10 months, it is calculated as February 1st of the current year.

      • To choose a specific day of a specific week in a specific year, select The. Choose the ordinal number (First, Second, and so on), day of the week, and the month from the drop-down lists. For example, if today is Wednesday, January 28th, then "today minus 10 months" and "the first Monday of January" is calculated as January 7th of the previous year. Without the offset of 10 months, it is calculated as Monday, January 5th of the current year.

      You can view the result of the offsets and date adjustments in the Preview area.

  8. Dynamic times are available for any attribute forms with a Datetime format. To define a dynamic time, do the following:

    1. Click the Time tab.

    2. To define a dynamic hour, click Dynamic Hour. Select Plus or Minus from the drop-down list. Type the offset value in hours. For example, if the current time is 12:40 PM, then "this hour plus one hour" is calculated as 1:40 PM.

    3. To define a dynamic minute, click Dynamic Minute. Select Plus or Minus from the drop-down list. Type the offset value in minutes. For example, if the current time is 12:40 PM, then "this minute minus 10 minutes" is calculated as 12:30 PM.

    4. You can offset the dynamic time by both hours and minutes by performing both of the previous actions. For example, if the current time is 12:40, then "this hour plus one hour" and "this minute minus 10 minutes" is calculated as 1:30 PM.

    : You can view the result of the offsets in thePreviewarea.

  9. Click OK to save the settings. You are returned to the Attribute Qualification pane.

  10. Finish creating the attribute qualification, beginning at this step.

Related Topics