Relative Date Filters

Dashboard Designer
#1

Hello Helical Insight,

I am using Helical Insight 4.0. On my dashboard, I want to give the option of relative date filters to the users. For example, Last Month, Last Year, Last Quarter etc. Apart from this, the user can also select the Date Range (startdate and enddate). I cannot find any such option for implementing relative date filters on the frontend. Is there a way to implement this?

Thank You,
Snow.

#2

In our future versions 5.0 onwards we are going to make implementing relative date range filter Date filters or date pickers work on the concept of start date and end date within Helical Insight. For relative date filters, we need to utilize the system date, based on which the start date can be determined, whereas the end date will be the system date.

For achieving this, please download this file (DynamicDate.zip (1.4 KB) ). Unzip this folder and place it in the hi-repository. Make sure that the folder name is DynamicDate.

In the .efwd file, provide connection ID of a working connection at the globalID tag (refer to below image). The connection id is basically the datasource id which you can see from the datasource page.
image


**NOTE** : *In the DynamicDate.efwd file there is a SQL written for with union clause. This SQL syntax is for MySQL. In case if you using any other DB which has a different syntax as compared to MySQL accordingly you should make that change.*

See below image to learn how to identify the connectionID
image



Create a report at the adhoc report level with StartDate and EndDate as the filters. Please note that the filter names should be exactly this. StartDate should have condition like greater than or equal to and EndDate should have condition like less than or equal to. Save this report.

On the dashboard designer, add the report with StartDate and EndDate as the filter which you have created.

Go to Objects section in the toolbar. Navigate to Dashboard Components -> Charts

image

The following popup will show. Click on Browse next to VF.
image

The file browser will open. Select the VF file present in DynamicDate folder.
image

Click on Save. You will see an empty component on the dashboard.
image

Right click on this component and add the following HTML:
image

<div class="col-sm-12 col-md-12 col-xs-12">
 <div class="col-xs-12 col-md-4 col-sm-4">
<div id="parameter3"></div>
</div>

 <div class="col-xs-12 col-md-4 col-sm-4">
<div id="parameter1"></div>
 </div>
<div class="col-xs-12 col-md-4 col-sm-4">
<div id="parameter2"></div>
 </div>
<div class="col-xs-12 col-md-4 col-sm-4">
<div id="parameter4"></div>
 </div>

<div class="col-xs-12 col-md-4 col-sm-4">
<div id="parameter5"></div>
 </div>
</div>

`This code basically adds div for every type of parameter. In the example attached, we have 4 options:

  • Last Month
  • Last Week
  • Last Year
  • Date Range

image

By default, Last Year is selected. You can modify the options as per your requirement. Accordingly, the VF needs to be modified.

When Date Range is selected, 2 datepickers appear on the screen corresponding to StartDate and EndDate.

image

Users can select the date range that they want to use.

Now, click on Edit (pencil icon) on the report and add StartDate and EndDate as listeners and input.

image

Refresh the dashboard. Now, you can see that the report responds to Last Month, Last Week, Last Year and Date Range filter values.

#3

In order to add any additional option like Current Day, Current Month etc.

You have to made changes at 2 places.

  1. Edit .efwd file provided and add those additional options whichever you like to add.

  2. Edit .efwvf file, add additional IF ELSE conditions for new options which you like to add. Below is sample of 2 additional option called Current Day and Current Month :

/* Below code will check the condition if value from drop down is having value Current Day or Current Month */

else if(Dashboard.getVariable(‘dropDownList’) == ‘Current Day’){

var today = new Date(); // today’s date

var dd = String(today.getDate()).padStart(2, ‘0’); // extract date like 01,02…,31

var mm = String(today.getMonth() + 1).padStart(2, ‘0’); //extract month like 01,02…12

var yyyy = today.getFullYear();// extract year

var today = yyyy + ‘-’ + mm + ‘-’ + dd; // form today’s date

Dashboard.setVariable(‘EndDate’,today);// set startdate

Dashboard.setVariable(‘StartDate’,today); );// set enddate

}

else if(Dashboard.getVariable(‘dropDownList’) == ‘Current Month’){

var today = new Date();

var firstDayOFMonth = new Date(today.getFullYear(), today.getMonth(), 1);// extract first date of current month

var dd = String(firstDayOFMonth.getDate()).padStart(2, ‘0’);

var mm = String(firstDayOFMonth.getMonth() + 1).padStart(2, ‘0’); //January is 0!

var yyyy = firstDayOFMonth.getFullYear();

var firstDayOFMonth = yyyy + ‘-’ + mm + ‘-’ + dd;

Dashboard.setVariable(‘StartDate’,firstDayOFMonth);

var lastDayOFMonth = new Date(today.getFullYear(), today.getMonth() + 1, 0);//extract last date of current month

var dd = String(lastDayOFMonth.getDate()).padStart(2, ‘0’);

var mm = String(lastDayOFMonth.getMonth() + 1).padStart(2, ‘0’); //January is 0!

var yyyy = lastDayOFMonth.getFullYear();

var lastDayOFMonth = yyyy + ‘-’ + mm + ‘-’ + dd;

Dashboard.setVariable(‘EndDate’,lastDayOFMonth);

}