Forum Discussion
Handle blank dates in CalendarAuto
Hi Community,
My dataset contains +15 Date columns, in order to apply time intelligence I used CalendarAuto() to create a date table.
Since my date columns accept blank values the date tablke started from 1899 🙄 which adds useless years to he calendar.
Is there any trick to get ride of those dates?
Thank you in advance.
You can use CALENDAR to define a more controlled range but still have it dynamic so it adds future years if needed. Something like this.
Dates = VAR _Start = DATE ( 2010, 1, 1 ) VAR _End = DATE ( YEAR ( TODAY() ) + 1, 12, 31 ) RETURN CALENDAR ( _Start, _End )This will give us a calendar table from 1/1/2010 - 12/31/2023
6 Replies
You can use CALENDAR to define a more controlled range but still have it dynamic so it adds future years if needed. Something like this.
Dates = VAR _Start = DATE ( 2010, 1, 1 ) VAR _End = DATE ( YEAR ( TODAY() ) + 1, 12, 31 ) RETURN CALENDAR ( _Start, _End )This will give us a calendar table from 1/1/2010 - 12/31/2023
Hi jdbuchanan71
Thank you for you reply.
As long as my dataset contains many date columns, using Calendar function will require including all of them, for this reason I try to find whether we can handle that issue using CalendarAuto.
Thank you in advance.
CALENDAR does not need to know anything about the other date columns in your model, you just set the start and end and it generated the calendar table. CALENDARAUTO just reads the date fields in your model and creates a single date table that covers them all. The only difference is you are controlling the start and end using CALENDAR but they both do the same thing, create a table with a single column of dates.
In order to define the start date I will have to go through all date columns to return the min date.
Internally, CALENDARAUTO calls CALENDAR,
Define a reasonable starting year by yourself.
Just set is low enough so you know you got them all, start it in 1/1/2000 or something.
