Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
provie98
Contributor II
Contributor II

Trouble with Master Calendar Time Date

I'm trying to create a master calendar table to attached to an Add_Date that I have in my Core_Activity Table.

I have the following script to do this:


LET min_calendar_date = Num(Floor(Peek('Add_Date',0,'CORE_ACTIVITY')));
LET max_calendar_date = Num(Floor(Peek('Add_Date',-1,'CORE_ACTIVITY')));
LET varToday = num(today());

TempCalendar:
LOAD
 
$(min_calendar_date) + rowno() - 1 as Num,
 
date($(min_calendar_date) + rowno() - 1) as TempDate
AUTOGENERATE
 
$(max_calendar_date) - $(min_calendar_date) + 1;

Master_Calendar:

LOAD
TempDate as Add_Date,
Week(TempDate)as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
ceil(month(TempDate)/3) as Quarter,
'Q' &
ceil(month(TempDate)/3) as QuarterName,
Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
inyeartodate(TempDate,$(varToday),0)*-1 As CurYTDFlag,
inyeartodate(TempDate,$(varToday),-1)*-1 as LastYTDFlag
RESIDENT TempCalendar

Order by TempDate ASC;
Drop Table TempCalendar; 

The problem I'm encountering is that my Add_Date has a time on it.  When I select Year off of the Master_Calendar table, it is only bringing back those dates time stamped of 12:00:00 AM.  Any idea how i can get it to bring back all the dates for a specific Year regardless of the time?  Any advice is greatly appreciated.

Thanks!

1 Solution

Accepted Solutions
DavidFoster1
Specialist
Specialist

In your Core_Activity table add an extra field (cal_date or somesuch) that is derived from the add_Date using:

daystart(add_date) note: dont use DATE function as that returns a string value.

Then use that date to build your calendar.

View solution in original post

7 Replies
Not applicable

Change the line

TempDate as Add_Date,

with

date(TempDate) as Add_Date,

Hope this helps

its_anandrjs

Hi,

In your TempCalendar table in below line change date script like

  date($(min_calendar_date) + rowno() - 1,'MM/DD/YYYY' ) as TempDate


put your region date format there


Hope this helps

Thanks & Regards

its_anandrjs

Another way is change script in Master_Calendar

Master_Calendar:

LOAD
Date(TempDate,'MM/DD/YYY') as Add_Date,


Hope this Helps

Thanks & Regards


provie98
Contributor II
Contributor II
Author

I tried all of the above suggestions and it's still not working.  I appreciate the input though.  If anyone else thinks of something let me know.  Thanks!

DavidFoster1
Specialist
Specialist

In your Core_Activity table add an extra field (cal_date or somesuch) that is derived from the add_Date using:

daystart(add_date) note: dont use DATE function as that returns a string value.

Then use that date to build your calendar.

Anonymous
Not applicable

Hi

The Master_Calendar script all looks fine to me, but I think David above is sort of on the correct track.

As he says add an extra field in your script to the Core_Activity table, I'd suggest :

     num(floor(add_date))          as cal_date ,

Then in your Master_Calendar script change this line :

     TempDate as Add_Date,

to this

     TempDate as cal_date ,

After a reload check in your table viewer that Master_Calendar is joining to Core_Activity via cal_date and not by anything else.

Best Regards,     Bill

provie98
Contributor II
Contributor II
Author

Thanks so much for your help.  This suggestion actually worked for me.