How to generate a report with rolling months using dynamic dates in MicroStrategy Developer
To obtain a report for sales in the last two years, use the Date Editor when creating the filter in Strategy Developer and execute the following steps:
Create a new filter and select the 'Add an Attribute qualification' option.
Select the attribute 'Date' and choose 'ID' from the Qualify on menu. Click on the calendar icon to the right of the date value in the Operator section.
The Date Editor window will appear. Select the Dynamic date and Today radio buttons. Choose the appropriate offset (in this example Minus 2 years Plus 1 month).
Click on 'OK' and repeat Steps 1 through 3 until the New Filter - Filter Editor window displays the rolling metrics as shown below. Double-click on the AND operator to change it to 'OR'.
Create a report using the following template:
This report filters on specific dates. Since the filter was today's date minus 2 years plus one month, the result is (for example) August 10, 1998, if today's date is July 19, 2000. To receive a full month's data, the metric must be made absolute. To do so, right-click on the metric from the Report Editor and select 'Edit'.
In the Metric Editor window under the Level (Dimensionality) section, select 'Report Level' under 'Target' and 'absolute' under 'Filtering'.
When run, the resulting report appears as shown below:
