Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Master Calendar/Canonical Calendar - Problem with data

Hi, 

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. 

Please help.

Thanks. 

QV calendar.PNG

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: Master Calendar/Canonical Calendar - Problem with data

Hi, 

The problem is not with your calendar script but with the way you are creating ClosedDate & CreatedDate.

Try

 

Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT')))

 

 

Anyway, FYI below is a better calendar script

 

Fact:
NoConcatenate
LOAD 
			[Ticket Number], 
			Category, 
			[Closed On], 
			[Created On], 
			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,
			[Due On], 
			Priority, 
			Status, 
			[Time Spent], 
			[Time To Resolve], 
			Impact, 
			Type, 
			Department,  
			Source, 
			[Problem Resolution], 
			Severity
Resident Ticket;
     
Drop Table Ticket;

DateBridge:
Load 
	[Ticket Number], 
	Created_Date as CalendarDate, 
	'Created' as DateType
Resident Fact;
     
Load 
	[Ticket Number],  
	Closed_Date as CalendarDate, 
	'Closed' as DateType
Resident Fact
;


Calendar_Created_Date:
NoConcatenate
LOAD
			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
While 		
	(MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
			min(FieldValue('CalendarDate', recno()))-1 	as MinDate,
			max(FieldValue('CalendarDate', recno())) 	as MaxDate
AUTOGENERATE FieldValueCount('CalendarDate');

 

 

vamsee_0-1593469101800.png

 

 

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

Re: Master Calendar/Canonical Calendar - Problem with data

Hi, 

The problem is not with your calendar script but with the way you are creating ClosedDate & CreatedDate.

Try

 

Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT')))

 

 

Anyway, FYI below is a better calendar script

 

Fact:
NoConcatenate
LOAD 
			[Ticket Number], 
			Category, 
			[Closed On], 
			[Created On], 
			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,
			[Due On], 
			Priority, 
			Status, 
			[Time Spent], 
			[Time To Resolve], 
			Impact, 
			Type, 
			Department,  
			Source, 
			[Problem Resolution], 
			Severity
Resident Ticket;
     
Drop Table Ticket;

DateBridge:
Load 
	[Ticket Number], 
	Created_Date as CalendarDate, 
	'Created' as DateType
Resident Fact;
     
Load 
	[Ticket Number],  
	Closed_Date as CalendarDate, 
	'Closed' as DateType
Resident Fact
;


Calendar_Created_Date:
NoConcatenate
LOAD
			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
While 		
	(MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
			min(FieldValue('CalendarDate', recno()))-1 	as MinDate,
			max(FieldValue('CalendarDate', recno())) 	as MaxDate
AUTOGENERATE FieldValueCount('CalendarDate');

 

 

vamsee_0-1593469101800.png

 

 

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Master Calendar/Canonical Calendar - Problem with data

Hi Lily, 

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. 

cheers,

Basav

Highlighted
Specialist
Specialist

Re: Master Calendar/Canonical Calendar - Problem with data

Hi @basavaraju_S 

FYI

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;
New_Ticket:
NoConcatenate
Load * Resident Ticket;
Drop Table Ticket;
Highlighted
Contributor II
Contributor II

Re: Master Calendar/Canonical Calendar - Problem with data

Hi @vamsee,

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. 

 

Highlighted
Contributor II
Contributor II

Re: Master Calendar/Canonical Calendar - Problem with data

Hi @basavaraju_S , 

Thanks for effort to help me.