Filtering date range using range slider – SAC

Overview:

Date could be filtered as individual members or list of predefined ranges through input controls in SAC with live models. But there is no direct way to adjust the date range at the runtime/view. By utilising the ability of the range slider element, runtime date range filtering could be performed. Lets look into the steps to successfully implement this functionality.

Range slider:

Range slider is a filter/control element available in both SAC analytical application and new unified story. Either manual(fixed input) or variables(dynamic input) could be fed into the slider data source.

Range slider builder panel

• Maximum and minimum value defines the range of the slider
• Start and end value defines the selected range value in the slider
• Accepts only number data type

Problem solution: Date range filtering using slider

1. Steps to filter by date

Basic idea: Mapping slider values as a corresponding index values of the date array to create an indirect linear relation

Co-relating slider and array

• Add range slider from the filters/controls element group
• Choose Script variables(To enter selected start and end value) and set the range of the slider (Min and max value)
• To dynamically set the range of the slider, go to page onInitialisation event
• Enter the following code

  // Page --> OnInitialization
  // Create a date array from the data source
  var dateArr = Chart_23.getDataSource().getMembers("2FZODS-CALENDAR_WEEK");
  var length_DtArr = dateArr.length;
  
  // Set the Slider maximum value to the length of the Array to match indexing 
  RangeSlider_Whole.setMaxValue(length_DtArr);
  
  // Initiate dynamic variables for selected range in the slider
  ScriptVariable_1=0;
  ScriptVariable_2= length_DtArr;
  ​

• To add the range filtering functionality, Add the following code in onChange event of the slider element

  // Get date as an array
  var dateArr = Chart_23.getDataSource().getMembers("2FZODS-CALENDAR_WEEK");
  
  // Using slider data as index value for date array
  var low = RangeSlider_Whole.getRange().getStartValue();
  var high = RangeSlider_Whole.getRange().getEndValue();
  
  // Create a new array with selected range from slider by slicing original date array 
  var slicedDate = dateArr.slice(low,high);
  
  // Filtering the date range 
  Chart_23.getDataSource().setDimensionFilter("2FZODS-CALENDAR_WEEK",slicedDate);
  ​

• To display the selected date range as dynamic text extract the dates and store them in global script variable[Start_date: String, End_date: String] as shown below

  // Display text in dynamic text
  var start = dateArr.slice(low,low+1);
  var End = dateArr.slice(high-1,high);
  
  // start{dtype: memberInfo} converted into String in the below code, As dynamic text accepts string| Int | array of strings not memberInfo dataType  
  Start_date = start[0].id;
  End_date = End[0].id;​

Output:

Before filtering

After filtering

2. Steps to filter by week: Only works when data is from a single year

In HANA modelling, create a week field by using SQL engine calculated column from the target date field

Calculated column in HANA: SQL Engine

• The range of the slider is fixed with min value = 1 and max value = 52
• The method setDimensionFilter() won’t accept datatypes apart from string/ array of string/ memberInfo/ array of memberInfo/ selections/ array of selections. The following code is used to extract, slice and convert the date array to filter the value by weeks.

  // Get min and max selected value
  var low =RangeSlider_1.getRange().getStartValue();
  var high=RangeSlider_1.getRange().getEndValue();
  
  // Creating an empty array of type string
  var rangeArray =ArrayUtils.create(Type.string);
   
  //Creating a continuous sequence of week between selected range
  for(var i=low;i<=high;i++)
  	{
  	var j = ConvertUtils.numberToString(i);
  	rangeArray.push(j);
  	}
   
  // Filtering the data based on selected number range
  Chart_23.getDataSource().setDimensionFilter("2FZODS-WEEK",rangeArray);​

Conclusion:

By utilising the scripting and the range slider element, The date range filtering was performed dynamically during runtime/view mode. Many such custom requirements would also be possible with the help of inbuilt scripting in SAC Analytical application/ Optimised design story.

References:

1. What-If Analysis using Slider and Range Slider

2. Configure sliders and range sliders

3. Dimension filters in Analytical application