Relative Date Usage

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.

Relative Date Usage

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.

Relative Date Usage

Now after setting the required condition, click on the ‘R’ to set the required condition.

Relative Date Usage

Relative Date Usage

Using the dropdown you can what Relative you would like to use and based on that the other options will change.

  1. 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)

  2. 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)

  3. 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)

  4. 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)

  5. 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)

  6. 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)

  7. 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.

Relative Date Usage

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

Relative Date Usage

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.

You May Also Like

Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered