MicroStrategy ONE

Dynamic Date Editor

The Dynamic Date Editor allows you to filter an attribute form with a fixed offset of the current date, such as one month ago. This is referred to as a dynamic date. For any attribute form with a Datetime or Time format type, you can also define a dynamic time.

You can access the Dynamic Date Editor when creating a stand-alone filter or a filter embedded in a report.

Creating a stand-alone filter

For more detailed instructions on creating a stand-alone filter, see Filtering data based on dates.

  1. On the New Filter page, select a date or time attribute, such as ship date or invoice date.
  2. Click the calendar icon to open the calendar, and click Switch to Dynamic Date. The Dynamic Date Editor opens.

Creating a filter within a report

For more detailed instructions on creating an embedded filter, see How to Create a Filter within a Report: Embedded Filters.

  1. Run the report in Design View.
  2. In the Report Filter panel, select a date or time attribute, such as ship date or invoice date.
  3. Click the calendar icon to open the calendar, and click Switch to Dynamic Date. The Dynamic Date Editor opens.

Dynamic dates

A dynamic date is either a fixed set of dates or a range of dates that are a fixed offset of the current date. For example, a dynamic date allows you to run a report that displays the sales for the past two months. This filter qualifies on a dynamic date of today with an offset of two months. Other examples of dynamic date qualifications are:

  • Today minus an offset of 2 months
  • Today minus an offset of 2 months and 1 day
  • Monday of this week
  • Monday of last week
  • The second of this month
  • The second of last month
  • The second of the current month, for last year (for example, if the current month is January, then the second day of January of last year)
  • The second day of the current quarter
  • The fourth Monday of this month
  • The fourth Monday of next month
  • The fourth Monday of the current month, for last year
  • The fourth Monday of the second month of the current quarter
  • Four days from the last day of this month
  • Four days from the last day of next month
  • Four days from the last day of the current month, for last year
  • Four days from the last day of the current quarter

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

For an example of a dynamic date, including instructions to re-create the example, see the Advanced Reporting Help

Dynamic time

A dynamic time is similar to a dynamic date, and is used for any attribute form with a Datetime or Time format type. A dynamic hour allows you to define a dynamic hour on which to qualify the attribute form, for example, the current time minus 1 hour or plus 4 hours. A dynamic minute allows you to specify a dynamic minute on which to qualify the attribute form, for example, the current minute minus 1 minute or plus 30 minutes.

Dynamic times work in the same manner as dynamic dates. Hours, minutes, or both can be calculated dynamically. 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.

To define a dynamic date

  1. On the New Filter page (if you are creating a stand-alone filter) or the Report Filter panel (if you are creating an embedded filter), select a date or time attribute, such as ship date or invoice date.
  2. Click Qualify.
  3. From the first drop-down menu, select ID or Date, depending on whether the attribute form has a Date or Datetime format.
  4. From the next drop-down menu, select the operator that describes how to filter data. To use a data range in your filter, select Between.

  5. Click the calendar icon to open the calendar, and click Switch to Dynamic Date.

    As you add date offsets or make date adjustments, the result is displayed in the Preview area.

  6. To offset the dynamic date, do one or both 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 date by days, select Plus or Minus from the drop-down before Days. Type the offset value in days.
    • To offset the date by months, select Plus or Minus from the drop-down before Months. Type the offset value in months.
    • To offset the dynamic date by both days and months, complete both of the previous actions.
  7. To exclude Saturdays and Sundays when calculating the dynamic date, select the Exclude weekends checkbox. For example, if today is Tuesday and the offset is today plus 4 days, the dynamic date would be Saturday. If this option is selected, the next day that would not be a Saturday or Sunday (in this case, Monday) is returned instead.

  8. To adjust the dynamic date, select the Apply additional adjustments checkbox, and then choose one of these options from the drop-down:

    • To select the day of the week to use as the adjustment, select Weekly, and then select a day from the Select a day drop-down.

      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.

    • To choose a numeric day of the month, select Monthly, and then select Day from the Monthly options. 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 Monthly, and then select The from the Monthly options. Choose the ordinal number (First, Second, and so on) and the day of the week from the drop-downs.

      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 Monthly, and then select Reverse Count from the Monthly options. 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.

    • To choose a numeric day of the quarter, select Quarterly, and then select Day from the Quarterly options. Type the (numeric) day of the quarter.

      For example, if today is October 6th, then "today minus seven days " is calculated as October 2nd (the second day of the current quarter, which runs from October through December). "Today minus seven days" and "Day = 2" calculates the dynamic date as July 2nd. This is calculated by subtracting seven days from the current day, resulting in September 29th. That date is in the third quarter, so the second day of the third quarter is July 2nd (the third quarter of the year runs from July to September).

    • To choose a day of the quarter, select Quarterly, and then select The from the Quarterly options. Choose the ordinal number (First, Second, and so on), the day of the week, and the month from the drop-downs.

      For example, if today is Tuesday, October 6th, then "the second Sunday" of the "second month" is calculated as Sunday, November 8th. (November is the second month of the current quarter.) "Today minus seven days" and "the second Sunday" of the "second month" is calculated as Sunday, August 9th. This is calculated by subtracting seven days from the current day, resulting in September 29th. That date is in the third quarter, so the second month of the third quarter is August (the third quarter of the year runs from July to September).

    • To offset from the last day of the quarter, select Quarterly, and then select Reverse Count from the Quarterly options. Type the offset value in days.

      For example, if today is October 6th, then "Reverse Count = 1" is calculated as December 31st, the last day of the current quarter (which runs from October through December), and "Reverse Count = 10" is calculated as December 22nd. "Today minus 10 months" and "Reverse Count = 10" is calculated as December 22nd of the previous year.

    • To choose the numeric day of a specific month, select Yearly, and then select On from the Yearly options. Choose the month from the drop-down 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 Yearly, and then select The from the Yearly options. Choose the ordinal number (First, Second, and so on), day of the week, and the month from the drop-downs.

      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.

  9. Dynamic times are available for any attribute forms with a Datetime or Time format; the Use Dynamic Time checkbox will be available.

    • To define a dynamic time, select the Use Dynamic Time checkbox, then do one of the following:

      • To offset the time by hours, select Plus or Minus from the This Hour drop-down. 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.
      • To offset the time by minutes, select Plus or Minus from the This minute drop-down. 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.
      • To offset the dynamic time by both hours and minutes, complete both of the previous actions. For example, if the current time is 12:40 PM, then "this hour plus one hour" and "this minute minus 10 minutes" is calculated as 1:30 PM.

    • To use a static time, clear the Use Dynamic Time checkbox. Type the static time in the Time field.

    10. You can view the result of the offsets in the Preview area.

  10. Click OK.

    11. To choose a static date instead of creating a dynamic date, click the Switch to dynamic date link. You are returned to the calendar to choose a date.

  11. Finish creating the filter, as described in Filtering data based on dates or Creating a filter within a report: Embedded filters.