In the previous blog of this series, we learned how to launch Smart Discovery from analytic applications in SAP Analytics Cloud. In this blog, let us look at Date and Time Range functions and how to use them.
Analytic Applications support passing single or multiple members for filtering but do not support ranges. However, for Date dimensions, you can pass the time range as a parameter. Here below you can see various time range filters in action.
Creating a Time Range
TimeRange is a parameter type that can be used to filter dimensions of type Date.
A Time Range can be created using the function TimeRange.create(), which takes three parameters namely TimeRangeGranularity, Start Date and End Date.
Syntax: TimeRange.create(TimeRangeGranularity, Start Date, End Date)
TimeRangeGranularitycan be of type Year, Halfyear, Quarter, Month, Day, Hour, Minute, Second or Millisecond as seen below.
For example, the following script can be used to create a month range between two dates.
TimeRange.create(TimeRangeGranularity.Month, new Date(2017, 12, 27), new Date(2019, 3, 19));
By simply changing the granularity type you can get other time ranges based on year, quarter, day, etc.
Time Range Functions
Apart from the create function for Time Range, you can also create time ranges using three more functions.
The syntax for these functions are as follows:
- Week Range – createWeekRange (start year, start week, end year, end week)
- Month Range – createMonthRange (start year, start month, end year, end month)
- Year Range – createYearRange (start year, end year)
For example, to create a range of months we can directly use the Month Range function as in the following script:
TimeRange.createMonthRange(2018, 2, 2019, 3);
Similarly, other time ranges can also be created by using these functions with appropriate parameters.
Dynamic Time Range
Let us see how to get the current date, month, quarter and year for dynamic filtering. You can generate the current (system) date using Date functions with Date.now() as the parameter. Once you have the local variable of type Date you can extract other information like a month, year, etc.
Since there is no function to extract quarter, you can use the Math function and extract quarter from current_month.
Now you can use these local variables in the TimeRange function to achieve dynamic filtering. Note that getMonth() function returns month as a value between 0 – 11 for January to December.
Also, note that you need to assign an appropriate hierarchy to the Date dimension for the Time Range filter to work properly.
The following example shows how to filter a chart to the last two quarters dynamically, using scripting. First, a TimeRange variable of granularity Quarter is created (QuarterFilter). Then the variable is used in the setDimensionFilter function as a parameter.
var current_date = new Date(Date.now());
var current_year = current_date.getFullYear();
var current_quarter = Math.floor(current_date.getMonth()/3);
var passed_quarter = 3;
var passed_year = current_year-1;
passed_quarter = current_quarter-1;
passed_year = current_year;
var passed_quarter_starting_month = (passed_quarter*3);
var passed_Quarter_date = new Date(passed_year, passed_quarter_starting_month, 1);
var QuarterFilter = TimeRange.create(TimeRangeGranularity.Quarter, current_date, passed_Quarter_date);
* * *
Reach out to our team here if you are interested to evaluate if SAP Analytics Cloud is the right tool for you.