topic Re: Master Calendar/Canonical Calendar - Problem with data in QlikView

Master Calendar/Canonical Calendar - Problem with data

Lily — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Master Calendar/Canonical Calendar - Problem with data

vamsee — Mon, 29 Jun 2020 22:18:34 GMT

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');

Re: Master Calendar/Canonical Calendar - Problem with data

basav — Tue, 30 Jun 2020 06:10:44 GMT

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

Re: Master Calendar/Canonical Calendar - Problem with data

vamsee — Tue, 30 Jun 2020 14:42:25 GMT

Hi @basav

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;

Re: Master Calendar/Canonical Calendar - Problem with data

Lily — Thu, 02 Jul 2020 03:27:01 GMT

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.

Re: Master Calendar/Canonical Calendar - Problem with data

Lily — Thu, 02 Jul 2020 03:32:01 GMT

Hi @basav ,

Thanks for effort to help me.