Discussion board where members can get started with Qlik Sense.
I am working on building master calendars and would like some tips to auto generate the current month's FUTURE dates.
I can create a future date and autogenerate all the dates between but would have to remember to update this anytime that predetermined date comes around.
I would also like to add the Work days, and Total days to my data.
Is there any easy way to accomplish this?
My current Calendar looks like:
LOAD Min(Salesdate) As MinDate
LET vMinDate = PEEK('MinDate',-1,'MinMaxDate')-1;
LET vMaxDate = '12/31/2017';
Drop Table MinMaxDate;
// Master Calendar for fiscal year
Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,
Dual(Month, fMonth) AS FMonth,
DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,
Load Year + IF(Month>=$(vFM), 1,0) As fYear,
Mod(Month-$(vFM),12)+1 As fMonth,
DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,
Year(Salesdate) As Year,
Month(Salesdate) as Month,
Week(Salesdate) as Week
LOAD Date(recno()+$(vMinDate)) As visitdate AutoGenerate vMaxDate - vMinDate;
Go to Solution.
LET vMaxDate = Floor(MonthEnd(Today(1)));
That seems to have done the trick. Can you help explain the Floor* syntax purpose in this for me? My understanding is that Floor rounds values down, which is why I tried Ceil* but was unlucky.
Correct, Floor() is rounding down:
Ceil() is rounding up.
I've used floor() to create an integer representing the last date of current month (monthend() will create a floating point value representing the last millisecond of the month).