Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikers,
I am writing this blog to demonstrate how we can build date range selections via script solutions.
Date range selections are quite common in any BI dashboard. There are many approaches using which we can build this solution in dashboard. Below are few of the approaches.
Option 1 - Create two data Island table (From Date & To Date). Select From date & To Date from data Island table and store the values in respective variables (vFromDate, vToDate). These variables now can be used in set analysis to get desire output.
Option 2 - Create From Date & To Date table in data model which is associated with Fact table.
There could be other ways also to achieve the above scenario.
Though option 1 is easy to implement, on large data set it could cause performance issue as we are using data Island tables.
I am going to share the Option 2. This option makes use of associative model using script solution. Basically date range selections populates all the dates within that range and associates it with source/fact table. Advantage of using this approach is we can make use of Qlik's associative model so that creating complex expressions are not required.
Below is the screenshot of 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