From the version 5.0 onwards, in Open Source BI Helical Insight, we have introduced a UI driven method of implementation of relative dates.
Relative dates are helpful when you want that the report/dashboard should automatically show data for a specific range when it opens like “Last 7 days”, “Last 1 month”, “Last 3 hours”, “Next Quarter” etc.
For exampleif you want to get the data only of today.If you use regular method to achieve by specifying the value in the filter you might have to manually change the date to today daily but the same can be achieved using Relative Dates functionality very easily. In this blog we are going to cover how to use Relative Dates filter.
Now, lets see how we can do this.
First drag the required date/datetime column in to the filter section and create the filter. Then in the filters you will observe ‘R’ Which stands for Relative date.
NOTE: In order to use relative dates the filtering condition should be set to something like Range, In between etc wherein it takes 2 values.
Now after setting the required condition, click on the ‘R’ to set the required condition.
Using the dropdown you can what Relative you would like to use and based on that the other options will change.
- Relative with Year: If you select Year you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This year: Will show data for the current year. In our example it will show data for (2023-01-01 00:00:00 to 2023-12-31 23:59:59)
Next year: Will show data for the next year. In our example it will show data for (2024-01-01 00:00:00to2024-12-31 23:59:59)
Previous Year: Will show data for the next year. In our example it will show data for (2022-01-01 00:00:00to2022-12-31 23:59:59)
Next : Here you can specify what value you would like to put. For example if you put 3, then it will show data for next 3 years. In our example, with 3 years, it will show data for (2024-01-01 00:00:00to2026-12-31 23:59:59)
Last: Here you can specify what value you would like to put. For example if you put 3, then it will show data for previous 3 years. In our example, with 3 years, it will show data for (2020-01-01 00:00:00to2022-12-31 23:59:59)
Year to Date: It will show the data from the start of the current year and till today. In our example since today is 13 November 2023, it will show data for current date current time(2023-01-01 00:00:00 to 2023-11-13 13:56:31)
- Relative with Quarter: If you select Quarter you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This Quarter: Will show data for the current quarter. In our example it will show data for the current running quarter (2023-10-01 00:00:00 to 2023-12-31 23:59:59)
Next Quarter: Will show data for the next upcoming quarter. In our example it will show data for (2024-01-01 00:00:00 to 2024-03-31 23:59:59)
Previous Quarter: Will show data for the previous quarter. In our example it will show data for (2023-07-01 00:00:00 to 2023-09-30 23:59:59)
Next: Here you can specify what value you would like to put. For example if you put 3, then it will show data for next 3 quarters. In our current example, with 3 quarters, it will show data for (2024-01-01 00:00:00 to 2024-09-30 23:59:59)
Last: Here you can specify what value you would like to put. For example if you put 3, then it will show data for previous 3 quarters. In our example, with 3 quarters, it will show data for (2023-01-01 00:00:00 to 2023-09-30 23:59:59)
Quarter to Date: It will show the data from the start of the current quarter till today. In our example since today is 13 November 2023, it will show data for quarter till today (2023-10-01 00:00:00to 2023-11-13 13:56:31)
- Relative with Month: If you select month you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This Month: Will show data for the current month. In our example it will show data for (2023-11-01 00:00:00 to 2023-11-30 23:59:59)
Next Month: Will show data for the next month. In our example it will show data for (2023-12-01 00:00:00to2023-12-31 23:59:59)
Previous Month: Will show data for the previous month. In our example it will show data for (2023-10-01 00:00:00 to 2023-10-31 23:59:59)
Next: Here you can specify what value you would like to put. For example if you put 3, then it will show data for next 3 months. In our example, with 3 months, it will show data for (2023-12-01 00:00:00to2024-02-29 23:59:59)
Last: Here you can specify what value you would like to put. For example if you put 3, then it will show data for past 3 months. In our example, with 3 months, it will show data for (2023-08-01 00:00:00 to 2023-10-31 23:59:59)
Month to Date: It will show the data from the start of the current month and till today. In our example since today is 13 November 2023, it will show data for data for entire month till today (2023-11-01 00:00:00 to 2023-11-13 15:04:00)
- Relative with Day: If you select day you will find options like below (assume that today is 13 Nov 2023 13:56:31)
Today: Will show data for today. In our example it will show data for (2023-11-13 00:00:00to2023-11-14 23:59:59)
Tomorrow: Will show data for tomorrow. In our example it will show data for (2023-11-14 00:00:00to2023-11-14 23:59:59)
Yesterday: Will show data for yesterday. In our example it will show data for (2023-11-12 00:00:00to2023-11-12 23:59:59)
Next: Here you can specify what value you would like to put. For example if you put 3, then it will show data for next 3 days. In our example, with 3 days, it will show data for (2023-11-14 00:00:00to2023-11-16 23:59:59)
Last: Here you can specify what value you would like to put. For example if you put 3, then it will show data for previous 3 days. In our example, with 3 days, it will show data for (2023-11-10 00:00:00to2023-11-12 23:59:59)
Day to Date: It will show the data from the start of the current day till the time of viewing. In our example since today is 13 November 2023 13:56:31, it will show data for current date current time(2023-11-13 00:00:00 to 2023-11-13 13:56:31)
- Relative with Second: If you select second you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This Second: Will show data for the current second. In our example it will show data for (2023-11-13 13:56:31 to 2023-11-13 13:56:31)
Next Second: Will show data for the next second. In our example it will show data for (2023-11-13 13:56:32 to 2023-11-13 13:56:32)
Previous Second: Will show data for the previous second. In our example it will show data for (2023-11-13 13:56:31 to 2023-11-13 13:56:31)
Next: Here you can specify what value you would like to put. For example if you put 3, then it will show data for next 3 seconds. In our example, with 3 seconds, it will show data for (2023-11-13 13:56:32 to 2023-11-13 13:56:34)
Last: Here you can specify what value you would like to put. For example if you put 3, then it will show data for past 3 second. In our example, with 3 seconds, it will show data for (2023-11-13 13:56:28 to 2023-11-13 13:56:30)
Second to Date: It will show the data from the start of the current secondtill. In our example since today is 13 November 2023, it will show data for data for entire month till today (2023-11-13 13:56:31 to 2023-11-13 13:56:31)
- Relative with Minute: If you select minnute you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This Minute: Will show data for the current minute. In our example it will show data for (2023-11-13 13:56:00 to 2023-11-13 13:56:59)
Next Minute: Will show data for the next minute. In our example it will show data for (2023-11-13 13:57:00 to 2023-11-13 13:57:59)
Previous Second: Will show data for the previous minute. In our example it will show data for (2023-11-13 13:55:00 to 2023-11-13 13:55:59)
Next: Here you can specify what value you would like to put. For example if you put 2, then it will show data for next 2mins. In our example, with 2, it will show data for (2023-11-13 13:57:00 to 2023-11-13 13:58:00)
Last: Here you can specify what value you would like to put. For example if you put 2, then it will show data for past 2 mins. In our example, with 2 mins, it will show data for (2023-11-13 13:54:00 to 2023-11-13 13:55:59)
Min to Date: It will show the for minute to date. In our example since today is 13 November 202313:56:31, it will show data (2023-11-13 13:56:00 to 2023-11-13 13:56:31)
- Relative with Hour: If you select hour, you will find options like below (assume that today is 13 Nov 2023 13:56:31)
This Hour: Will show data for the current hour. In our example it will show data for (2023-11-13 13:00:00 to 2023-11-13 13:59:59)
Next Hour: Will show data for the next hour. In our example it will show data for (2023-11-13 14:00:00 to 2023-11-13 14:59:59)
Previous Hour: Will show data for the previous hour. In our example it will show data for (2023-11-13 12:00:00 to 2023-11-13 12:59:59)
Next: Here you can specify what value you would like to put. For example if you put 2, then it will show data for next 2 hours. In our example, with 2, it will show data for (2023-11-13 14:00:00 to 2023-11-13 15:59:59)
Last: Here you can specify what value you would like to put. For example if you put 2, then it will show data for past 2 hours. In our example, with 2, it will show data for (2023-11-13 11:00:00 to 2023-11-13 12:59:59)
Hour to Date: It will show the for hour to date. In our example since today is 13 November 2023 13:56:31, it will show data (2023-11-13 13:00:00 to 2023-11-13 13:56:31)
Usage of Anchor:
In all of the above covered examples, the relative dates (like last hour, last min, next hour, next quarter etc) is all happening with respect to the current date current time. In case if you would like to use some other date / date time as anchor to calculate these things (like last hour, last min, next hour, next quarter etc) then Anchor functionality is helpful.
After you tick this option you will be given field to enter the date which acts as an anchor for the various functions (this year , next year etc) calculations to work.
Example: If you select Anchor year, you will get the data of the year that is present in the given date.If you select Anchor Next Year, It will give the data of next year based on the date give in anchor field
Here we gave 2013-11-10 13:02:56 as an anchor date. Now if you select Anchor Next Yearyou will get the data of 2014 year irrespective what the current year is.
If you select Next and enter 3 then you will get next 3 years and if you select Last you will get last 3 years.
In a similar way anchor will work for other kind of functions and parameters also.