Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sep 25, 2024 2:08:28 PM
Feb 19, 2017 6:28:07 AM
Date range selections are a common feature in any BI dashboard, and there are several ways to implement this functionality. Below are a few options:
Option 1: Create two data island tables (From Date & To Date). Users can select the From and To dates from these tables, and the selected values can be stored in variables (vFromDate, vToDate). These variables can then be used in set analysis to produce the desired output.
Option 2: Create From Date and To Date tables within the data model, associating them with the Fact table.
There are other approaches to achieve this scenario as well.
While Option 1 is easy to implement, it may lead to performance issues with large datasets due to the use of data island tables.
I will demonstrate Option 2, which utilizes an associative model through a script-based solution. In this approach, date range selections populate all the dates within the selected range and associate them with the source/fact table. The advantage of this method is that it leverages Qlik’s associative model, eliminating the need for complex expressions.
Below is a screenshot of the model
Update:
Added FromWeek-ToWeek and FromMonth-ToMonth functionality
Added qvf file
Feel free to provide any suggestions.
Regards,
Kushal Chawda
Simple and Elegant solution vs Going with Option 1.
Hi Kushal,
Good and informative blog.
- If you can add some technique so that Start date must not be more than End date and vise versa.
This can be achieved through field trigger but, is there any better approach.
Thanks for Sharing,
Cheers,
Pankaj