Date picker predefined ranges

[dia2021]

Hi Everyone,

I am using a date calendar on the dashboard, which is working fine. I need to use predefined ranges in the calendar. Could anyone help me to understand how does predefined ranges work?

Thanks in advance!

[anthonyj]

Hi @dia2021 ,

Here's an example of a predefined date range that I use in a report to point out dates last month, this month and year to date but you can define whatever period you like.

Firstly, to mitigate the issue of rolling the data over too early so that the first of the month is still looking at the previous month's data, I set a reporting month variable. (If this is not an issue you can use the "Today()" function.

// Set the reporting date to the day before so it won't roll over on the first of the month
let vRep = if(num(WeekDay(today())) = 1 and day(today()) < 4, num(date#(monthend(Today(),-1),'D/M/YYYY')), today()-1);
set vReporting = $(vRep);

// Clean up variables
let vRep=;

//Then I create a table calling out the periods and the start and end date of each period as of the reporting date. This is dynamic based on the load. I use the dual function so the periods will order correctly in a drop down filter.

Ranges:
Load
pick(match([DateRange], 'Last Month', 'This Month', 'Year To Date'), dual('Last Month', 0), dual('This Month', 1), dual('Year to Date', 2)) as [Date Range],
Date(Evaluate(RangeStart)) AS [Range Start],
Date(Evaluate(RangeEnd)) AS [Range End]
;
LOAD * INLINE [
DateRange|RangeStart|RangeEnd
Last Month|MonthStart(vReporting,-1)|MonthEnd(vReporting,-1)
This Month|MonthStart(vReporting)|vReporting
Year To Date|vHalfYear|vReporting
]
(ansi, txt, delimiter is '|', embedded labels);

// I then use an interval match on my main date column to join the Range table to my table that holds the dates. I'm using a left join as I only need to highlight the dates that fall within the predefined periods.

Left Join (Ranges)
IntervalMatch ([_DateKey])
Load distinct [Range Start], [Range End]

Resident Ranges;

I hope this gives some context as to how this can be used.

Thanks

Anthony

[dia2021]

@anthonyj Thanks for the quick reply.

The above script is working fine. Can you also explain how I can use all the fields at the dashboard level? I tried plugging in all the fields, but it is not working as expected. I appreciate your help!

[anthonyj]

Hi @dia2021 ,

I'm happy it's working for you. I'm not exactly sure what you're explaining the issue is here. The fields in the Range table should be available to be added to a filter object and the associations through the join to your master calendar column should drive the interaction. Could you clarify if this isn't what you mean?

Thanks

Anthony

[ersan_duran]

I understand the logic of creating custom list of period values. However, I am struggling to understand how to use those values in "Predefined Ranges" in Date Picker object. Please see below, I cannot change predefined values in the object. 

It seems like predefined range values are hard coded in the extension code. For example: Today will always show today's date (April 9, 2024). Formula in the predefined filter section impacts only the name of the predefined range (you can change the name to yesterday but data still will be filtered for today). 

I want to use: "Last Month", "last 3 months", last 6 months", "last 12 months" in predefined ranges. I created a new table with a field containing those values and linked ranges to calendar table but cannot really understand how I can populate those 4 custom range values in predefined range section in date picker object.

Is anyone able to use "Predefined Ranges" in Date Picker object? IS there any way to populate there custom defined values?

I can still use the classical filter object to filter my data for custom ranges I defined as below, but this is not what I need. 

[anthonyj]

Hi @ersan_duran,

According to the documentation of the data picker, you're right. There's no capability to change the predefined ranges supplied in the extension.

What you've created sounds like the way to go and I don't know if you're using Qlik Cloud or Enterprise but the latest version of the filters has the ability change your filter options to radio buttons etc... Or alternatively you can add a function to a series of buttons with a variable or bookmark.