Generate an interval of all possible dates between dates selected

[Thathayoyo]

Hello,

I have a table [ID_DETAILS] like this:

I want to find a way to put in a variable all possible dates between dates selected.

For exemple if i select 20/02/2024, 22/02/2024, 24/02/2024 then my variable need to contains:  20/02/2024, 21/02/2024, 22/02/2024, 23/02/2024, 24/02/2024.

Can we create it from an expression base on Min(Date_id) and Max(Date_id) selected? Can we create it from Loading Data (either SQL or Qlik) by creating a table [Calendar] containing all possible dates between Min(Date_id) and Max(Date_id)?

If you know how to implement it.

Thanks.

[Clement15]

Hello,

You can use the Intervalmatch() function

IntervalMatch | Qlik Sense on Windows Help
Exemple de correspondance d'intervalle – avec des dates - Qlik Community - 1488212

Résolu : IntervalMatch - Communauté Qlik - 2433139

[BuildItStrong]

A calendar create function has long been a helpful tool in Qlik.  I once created a task in each load script that created a calendar for each date of concern in my dashboard.  Today, because these calendars are just so helpful, and because we need them across many dashboards, and because we want to make sure that they are the same across all of our dashboards, we create our calendar in a database and reference that calendar across all of the dashboards that need this functionality.  In our case, we add calendar based info such as weekend flags, company holiday flags, Year, Month, Quarter.  Our accounting calendar is slightly different than the standard calendar.  Our start of month, start of FY, work week, work month, work quarter are different from the standard calendar, so our calendar includes those features.

There are many Qlik community posts about creating calendars.  Here is one such post:

https://community.qlik.com/t5/New-to-Qlik-Analytics/How-to-create-master-calendar-in-Qlik-Sense/td-p...

When you create the date of the calendar, you change the name in the load script to match the name of the date that you want to filter on with the calendar.  For instance, If you have a date like Date_id in your analytical data, you would change the name of the date field of your calendar to also be Date_id.  You can now choose data for a month, a week, a Monday, a year, etc. by adding these filter selections from your calendar into your filter pane.

Also, note that you will want to add a different instance of a calendar for each date that you want to filter on inside of your dashboard.  Said another way, if you have a date in your analytical data called called Date_id and another date called Work_Date_id, and you wanted to be able to filter on both of those dates, you would want to load two instances of a calendar (with one calendar date named Date_id and another named Work_Date_id), so  that you could filter on each of these separately.

I hope this helps,

BuildItStrong

[Dalton_Ruer · Accepted solution]

Check out my post Hungry Hungry Hippos where I walk through how to build a Master Calendar on the fly and then show how to utilize it with the IntervalMatch function. https://qlikdork.com/2023/06/hungry-hungry-hippos/

[Ben_P]

If you want to put them in a variable, then you could build a valueloop to create the list (incrementing the value by 1 each time), and then concat the values in the list. So define the variable as something like:

=concat(valueloop(vDateStart,vDateEnd,1),',')

or if you want to format the dates in a certain way, something like:

=concat(date(valueloop(vDateStart,vDateEnd,1),'DD/MM/YYYY'),',')

ValueLoop - chart function | QlikView Help

[BuildItStrong]

Great and Wise is the Dork!

[Dalton_Ruer]

Awwee shucks. Thank you so much.