SAP Knowledge Base Article - Public

3433918 - Filter by Range option is not available for Date Dimension

Symptom

 Filter by Range option is not available for Date dimension in SAC.

Environment

  • SAP Analytics Cloud
  • SAP Business Warehouse
  • SAP BW/4HANA

Reproducing the Issue

  1. Create a Story based on BW Query with Date field like: "Calendar Year/Week" or "Calendar Year/Month"
  2. Try to create a Filter by Range on this dimension
  3. Open filter dialog for this characteristic and confirm the Filter by Range option is not available

Cause

To be compatible with SAC range filters, SAC requires the type of a date dimension to be 0DATE or a reference to a characteristic 0DATE.

The date type dimension needs to follow the format described in the following SAP help article: Restrictions to SAP HANA and BTP Live Data Connections - Stories

(Note that the document refers to HANA connection but the restriction date ID format equally applies to BW non-hierarchical dates as well.

According to the SAP Help page chapter mentioned above, only 0CALDAY is a reference characteristic to 0DATE, and that is why this is the only characteristic able to provide the filter by range option.

In short, the "Calendar Year/Week" and "Calendar Year/Month" dimensions don't have the properly ID format, that is why the range filter option is not available.

In addition to making sure the semantic type is correct in order for the dimension to be compatible with date range filter feature, the structure of the dimension itself also needs to be correct. E.g:

For the "Calendar year" and "Calendar year/week" dimensions, their ID consist of just the year number (e.g. 2020) or the year-week number (e.g. 01.1995).  These are not compatible with date range filter either (unlike the "Calendar day" dimension).  

Resolution

For the characteristic to have the range filter option, make sure the dimension is of type date or reference to 0DATE and not other types like text or string.

Usually "Calendar day" (0CALDAY) has the the semantic type "date" or is a reference to characteristic 0DATE, this is why the range filter option will be enabled in SAC.

Keywords

KBA , LOD-ANA-ML , SAC Modelling , BW-BEX-OT-BICS-INA , BW INA provider , How To

Product

SAP Analytics Cloud 1.0