MicroStrategy ONE

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.

To create this report, follow the procedure below.

  • This procedure assumes that you are familiar with creating a conditional metric. For information, see Conditional Metrics.
  • MicroStrategy Tutorial may not contain information for the current date; if the report does not return any data, adjust the dates accordingly. For example, you could create qualifications for six months ago and for 8 months ago. If you are working with your own data, rather than Tutorial data, the current date calculation works if the project contains data for the current date.

To Create an Attribute Qualification Using a Dynamic Date

Create the Today Filter

This filter, which will be used on the report, qualifies on the current date.

  1. In MicroStrategy Developer, choose File > New > Filter.

    If the New Filter dialog box is displayed, click the Empty Filter icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

  2. Drag the Day attribute from the Object Browser to the Filter definition pane.
  3. Select ID from the Qualify On drop-down list.
  4. Click the Date Editor icon (the calendar).
  5. Select Dynamic date.
  6. By default, Today is selected. Note that the current date appears in the Preview area.
  7. Click OK, and then OK again to return to the Filter Definition pane. Notice that the qualification is displayed as Day(ID) Exactly Today.
  8. Save and close the filter, naming it Today.

Create the Today - 2 Months Filter

This filter subtracts two months from the current date. It will be used on the conditional metric.

  1. Begin creating a new filter, as described in the steps above, through selecting Dynamic date.
  2. By default, Today is selected. In the boxes next to months, select Minus and 2. This indicates an offset of two months ago. Note that the current date and the dynamic date appear in the Preview area.
  3. Click OK, and then OK again. Notice that the qualification is displayed as Day (ID) Exactly Today minus 2 months.
  4. Save and close the filter, naming it Today - 2 months.

Apply the Today - 2 Months to a Metric

Create a conditional metric to calculate the revenue on the dynamic date.

  1. Choose File > New > Metric.

    If the New Metric dialog box is displayed, click the Empty Metric icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

  2. Drag the Revenue fact from the Object Browser to the Metric Definition pane.
  3. Select Condition in the Metric component pane.
  4. Drag the Today - 2 months filter from the Object Browser to the Condition definition pane.
  5. Save and close the metric, naming it Revenue (2 months ago).

Create the Report

  1. Choose File > New > Report.

    If the New Grid dialog box is displayed, click the Empty Report icon. If you do not want this dialog box to be shown in the future, select Don't show this dialog in the future. Click OK. For a full description of object templates, including a list of the objects that can use object templates, see Re-Using Objects Via Shortcuts and Object Templates .

  2. Add the Region attribute, the Revenue metric, and the Revenue (2 months ago) metric.
  3. Drag the Today filter from the Object Browser to the Report Filter pane.
  4. Save the report, then execute it.