Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
erickd1190
Not applicable

Generate the remaining days of the current month

HI all,

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:

MinMaxDate:

LOAD Min(Salesdate) As MinDate

Resident ABC;

LET vMinDate = PEEK('MinDate',-1,'MinMaxDate')-1;

LET vMaxDate = '12/31/2017';

Drop Table MinMaxDate;

// Master Calendar for fiscal year

Calendar:

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,

   *;

Load visitdate,

Year(Salesdate) As Year,

Month(Salesdate) as Month,

Week(Salesdate) as Week

Resident ABC;

LOAD Date(recno()+$(vMinDate)) As visitdate AutoGenerate vMaxDate - vMinDate;

Tags (1)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Generate the remaining days of the current month

Maybe like

LET vMaxDate = Floor(MonthEnd(Today(1)));

3 Replies
swuehl
Not applicable

Re: Generate the remaining days of the current month

Maybe like

LET vMaxDate = Floor(MonthEnd(Today(1)));

erickd1190
Not applicable

Re: Generate the remaining days of the current month

Hey Stefan,

Thank you!

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.

swuehl
Not applicable

Re: Generate the remaining days of the current month

Correct, Floor() is rounding down:

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/GeneralNumericFunc...

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).