Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Ruthie09
Contributor III
Contributor III

Need formula for Date Picker custom ranges

I have been researching this for a couple of hours now and can't seem to find an answer.

I am using the VizLib Calendar extension (date picker) and the date ranges offered don't meet my needs.  If, for example, I wanted the last 3 months of data, what I would get using the built in options would pull a rolling 3-month period.

However, what I'm looking for is a formula that would capture the last 3 months since the 1st of that month.  For example, today Sept 11.  I need a formula that would grab all dates since June 1st.

Anyone have any ideas?

Labels (3)
6 Replies
joe_warbington
Luminary Alumni
Luminary Alumni

Hi Ruthie, 

You can quickly add custom date ranges to the Vizlib Calendar. The steps are detailed here: https://community.vizlib.com/support/solutions/articles/35000131319-vizlib-calendar-data-ranges

Ruthie09
Contributor III
Contributor III
Author

I am familiar with those but as I mentioned, when I configure it for the last 3 months, I get a rolling three months (meaning I would get since June 11).  What I need is the 1st of that month.

joe_warbington
Luminary Alumni
Luminary Alumni

Right - you can use the expression editor and the function MonthStart() for this purpose: https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

Ruthie09
Contributor III
Contributor III
Author

I must be doing something wrong because this doesn't work:
=MonthStart(Today(),-3)
which I think is what that doc says.
piersbatchelor_vl

Hey Ruth,

 

sounds like you need this:

 

monthstart(  addmonths( [MyDate], -3)  )

johngouws
Partner
Partner

Hello Ruth, 

I am not sure if you came right with the date picker custom range? I have been having the same issue for a long time - so eventually gave up!! I recently started fiddling with the date picker again, because it's pretty cool. I am glad to say I finally got it working, for me. Maybe you can adapt it to your needs. 

The MOST important thing I found that needs to be considered, is the date format date picker requires. A suggestion I found was to change your computer Regional date format to 'DD/MM/YYYY'! Be careful of doing that. It could have a knock on affect with other applications. 

This is how I overcame the format issue and the changes I made in the Date Picker Advanced Setup: 

1) In my calendar subroutine I created a new date field - My default date format is 'YYYY-MM-DD', DatePicker has a different format. In my case [$(_field)] = "Date". 

date([$(_field)],'DD/MM/YYYY') as [$(_prefix)PickerDate]

2) Changes to the Advanced Setup fields: 

- Min date: =Min( {1} [PickerDate])

- Max date: =Date(Today(),'DD/MM/YYYY')
//Max( {1} [PickerDate]) - Original
 //--> I use Today() because I have Target dates into the future.  

- Start date: =date(date#(MonthStart(max(Today()),-4),'YYYY-MM-DD' ), 'DD/MM/YYYY')
//date(date#(AddYears(YearStart(max(Today()),0),0),'YYYY-MM-DD' ), 'DD/MM/YYYY')
//Min([PickerDate]) - Original

//--> My requirement is to always show the past 4 full months. The second option sets to Year Start.  

- End date: =Date(Today(),'DD/MM/YYYY')
//Max([PickerDate]) - Original

Now when I Select date range, it starts from 4 full months ago. The < arrow allows the User to navigate further back.  

Default opens 4  full months agoDefault opens 4 full months ago

 

Hope this is of help. 

Stay Safe - John