Other relative date filters

Other types of relative date filters are often used, especially for the fiscal year in the business sector. A fiscal year varies with each business and is based on how that business calculates its annual financial statements. You can define a Fiscal Calendar dimension in your Mondrian schema to accommodate this calculation, so Analyzer uses the current date to look up fiscal time periods in the fiscal time dimension.

For example, a business may define their fiscal year to always start on the first of May. Their fiscal time dimension table would look like the following table:

Looking at the table and using a date such as 2014-05-01, we can find which Fiscal Week, Fiscal Month, Fiscal Quarter, or Fiscal Year that it belongs to. Just look for the date in the table, then look further up the hierarchy to find 2015-M1. If you need to get the Current Month and Previous Month, you can first find 2015-M1 and then look back on the hierarchy to find 2014-M12, which is a sibling of 2015-M1 in the hierarchy.

There are a few key points to keep in mind about this dimension, before you get started:

• The bottommost level must be a Date, which will be used to look up a parent-level member based on the current date.
• The Date level must specify a new AnalyzerFiscalDateFormat annotation. This annotation value should specify a Java format string, which when evaluated with the current date, yields the MDX name of the Date level member. This format string should not include the format string for any parents above the Date level. This is different from the AnalyzerDateFormat annotation in which parents are also included in the format string.
• The Date level members must be unique within the level, so uniquemembers is set to true. This does not need to be the same for parent levels, but it is a good practice to do so since this is a time dimension.
• All levels in this hierarchy need to specify the levelType attribute.
• Levels above the Date level should not specify the AnalyzerDateFormat annotations.

Here is an example of a Fiscal Calendar dimension defined within a Mondrian schema:

<Dimension name="Fiscal Calendar" type="TimeDimension">
  <Hierarchy hasAll="true" primaryKey="DATE_KEY">
    <Table schema="FOODMART" name="CALENDAR"/>
    <Level name="Fiscal Year" levelType="TimeYears" column="FSC_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_YEAR" />
    <Level name="Fiscal Quarter" levelType="TimeQuarters" column="FSC_QUARTER_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_DIM_QUARTER_NUM" />
    <Level name="Fiscal Month" levelType="TimeMonths" column="FSC_MONTH_YEAR_STR" uniqueMembers="true" type="String" ordinalColumn="FSC_DIM_MONTH_NUM" />
    <Level name="Fiscal Week" levelType="TimeWeeks" column="FSC_WEEK_YEAR_STR" uniqueMembers="false" type="String" ordinalColumn="FSC_DIM_WEEK_NUM" />
    <Level name="Date" levelType="TimeDays" column="CAL_DATE" uniqueMembers="true" type="Date" ordinalColumn="DATE_KEY" >
      <Annotations><Annotation name="AnalyzerFiscalDateFormat">[yyyy-MM-dd]</Annotation></Annotations>
    </Level>
  </Hierarchy>
</Dimension>

With this set up, Analyzer will be able to generate the MDX to turn a filter like Current Month into the correct Fiscal Month member:

Ancestor([Fiscal Calendar].[Date].[1997-06-28],[Fiscal Calendar].[Fiscal Month])

This MDX references a specific date member in the Date level, and then uses the Ancestor function to locate the parent month. Finding the Previous Month would be as simple as using the Lag MDX function:

Ancestor([Fiscal Calendar].[Date].[1997-06-28],[Fiscal Calendar].[Fiscal Month]).Lag(1)

Once you have these set up, your users will be able to apply this filter by selecting Choose a commonly used time period in the Filter on Fiscal Month dialog box.