Need formula for Date Picker custom ranges

[Ruthie09]

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?

[joe_warbington]

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]

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]

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]

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]

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 ago

Hope this is of help. 

Stay Safe - John

[CintiaK]

Hello @johngouws 

I saw your comment on this topic and I'm interesed about how you managed to create a master calendar with a date picker and dates in the future ! 

I'm starting to forecast some data and I would like to implement this onto my system; that works with a master calendar, as of months and the extension date picker. 

Thank you in advance, best regards,

Cin

[johngouws]

Hi @CintiaK . 

I can help with the first part of your question, the Calendar. In terms of dates into the future, the particular App I was working on included annual targets. That meant I had dates to the end of each year. What you can try is, at the bottom of the Subroutine there are DateMax fields commented out. Try using that instead of the standard DateMax. See what it does!

This is the script I use for the Calendar. Its in two parts. Load the Calendar parameters before loading your data and after the load, call the subroutine. In my case you will see in the CALL my date field is called Date. 

I hope this helps. 

/*Insert the Calendar parameters before loading the data*/

//===================================================
// Subroutine to Generate Calendar.
//===================================================
SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
LOAD
[$(_field)]
,Year(YearStart([$(_field)],0,$(FirstMonthOfYear))) as [$(_prefix)Year]
,Year(YearStart([$(_field)],0,$(FirstMonthOfYear)))&Num(Mod(Month([$(_field)]) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)YearPeriod]
,Year(YearStart([$(_field)],0,$(FirstMonthOfYear)))&' '&upper(month([$(_field)])) as [$(_prefix)Year Month]
,'Q' & Ceil(Month([$(_field)])/3) as [$(_prefix)Quarter]
,YearName([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearName]
,YearStart([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearStart]
,YearEnd([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearEnd]
,inyeartodate ([$(_field)], Today(), 0, $(FirstMonthOfYear)) as [$(_prefix)inYTD]
,inyeartodate ([$(_field)], Today(), -1, $(FirstMonthOfYear)) as [$(_prefix)inPYTD]
,inmonthtodate ([$(_field)], Today(), 0, $(FirstMonthOfYear) ) as [$(_prefix)inMTD]
,inmonthtodate ([$(_field)], Today(), -1, $(FirstMonthOfYear) ) as [$(_prefix)inPMTD]
,month([$(_field)]) as [$(_prefix)Month]
,num(month([$(_field)]),'00') as [$(_prefix)Period]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
,Year([$(_field)])*12 + Month([$(_field)]) as [$(_prefix)_BaseMonth]
,date([$(_field)],'DD/MM/YYYY') as [$(_prefix)PickerDate]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)]
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
// ,YearEnd(Today()+365) as DateMax 

// ,date(Today()+365) as DateMax 
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB

LOAD your Data ......

/*Generate Calendar Tab After loading the data*/
CALL CalendarFromField('Date', 'Calendar', '');