Dynamic Dates: Filtering by Offsetting the Current Date

When you qualify on a date attribute form with the date data type, you can select dynamic dates, which are fixed offsets of the current date. They can be either a fixed set of dates or different date ranges that change through time. For example, 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, weeks, months, or years (the offset) is always done first, before "first of this month," "this week," "this year," and so on is calculated. For example:

• If today is February 13th, then "today minus seven days" is February sixth, and "the first of the month of today minus seven days" is February first.
• However, if today is February second, then "today minus seven days" is January 26th, and "the first of the month of today minus seven days" is January first.

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, as described in the MicroStrategy Developer help.

You use the Date and Time Editor to create a dynamic date in an attribute qualification. When you qualify on a date attribute, a calendar icon is displayed; click it to open the Date and Time Editor.