Discussion board where members can get started with QlikView.
I've tried to create a canonical calendar for my qlikview project using sample script form forum community but it's seems not working fine. The data displayed is not same as my uploaded data.
Data not display in column field 'ClosedDate' and 'CalendarDate'. Supposedly, the data in 'Closed date' field must be exactly same as 'Closed on' field.
The problem is not with your calendar script but with the way you are creating ClosedDate & CreatedDate.
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT')))
Anyway, FYI below is a better calendar script
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT'))) as Created_Date,
Date(Floor(Timestamp#(Left([Closed On],len([Closed On])-3), 'MM/DD/YYYY hh:mm TT'))) as Closed_Date,
[Time To Resolve],
Drop Table Ticket;
Created_Date as CalendarDate,
'Created' as DateType
Closed_Date as CalendarDate,
'Closed' as DateType
Date(Calendar_Date, 'MM/DD/YYYY') as CalendarDate,
Year(Calendar_Date) as [Calendar Year],
'Q' & Ceil(Month(Calendar_Date) / 3) as [Calendar Quarter],
Month(Calendar_Date) as [Calendar Month],
Day(Calendar_Date) as [Calendar Day],
Week(Calendar_Date) as [Calendar Week];
Load Date(MinDate + IterNo() -1 ) as Calendar_Date
(MinDate + IterNo() - 1) <= Num(MaxDate);
min(FieldValue('CalendarDate', recno()))-1 as MinDate,
max(FieldValue('CalendarDate', recno())) as MaxDate
View solution in original post
I see the [Closed On] column contains timestamp. So can you try using the below expression in the script.
Date(Floor(Timestamp#([Closed On],'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as [Closed date],
I would have tested it my self but I do not the the excel file.
If you notice the timestamp contains timezone "UTC". Your expression does not address that. As @Lily shared the QVW you can do a binary load and use it to play around.
Binary [test calendar.qvw];
Drop Tables DateBridge, Calendar;
Load * Resident Ticket;
Drop Table Ticket;
I'm glad with your solution. Now I understand why my calendar is not working. You're right, it is because of my Created and Closed Date script is not in the correct format.
And thanks again for the calendar script given.
Hope, this script will help others too.
Hi @basavaraju_S ,
Thanks for effort to help me.