topic Re: How to get data to associate with Calendar in QlikView

How to get data to associate with Calendar

deepakqlikview_123 — Thu, 17 Oct 2013 08:23:33 GMT

Hello Experts,

Can you please tell me about hoe to get data to associate with calendar in qlikview.

Thanks in advance.

Regards,

Deepak

Re: How to get data to associate with Calendar

rustyfishbones — Thu, 17 Oct 2013 08:32:21 GMT

you need to link the Calendar Table to an event table

So if you have a Date field in your event table and a Date field in your Calendar table they should Join Automatically

It will look like this when you press CTRL T

Re: How to get data to associate with Calendar

Peter_Cammaert — Thu, 17 Oct 2013 08:45:49 GMT

A Calendar object is easy to create. Just attach it to a Field that contains date values, like for instance an OrderDate field in a transaction table.

However, like you may have noticed, this construction will produce a calendar with "holes" e.g. dates without sales (and without transactions) will not be available in your calendar.

To solve this problem and produce a continous calendar that will show all dates, even if there are no sales recorded, we create a so called Master Calendar. This is a separate table that contains an uninterrupted series of dates from a specific starting date up until an end date (usually equal to today()). The Master Calendar will also contain calculated subfields like Year, Month, Day, Weekday, Quarter AND - most important - a link field that has the same name as the Date field in your transaction table (see screenshot in Alans post).

Master Calendars are generated using a small piece of standard script code. See this discussion How to make a calendar? for an example. You can almost copy-and-paste the code into your document.

Good luck,

Peter

Re: How to get data to associate with Calendar

ali_hijazi — Thu, 17 Oct 2013 08:50:50 GMT

I've attached a script to generate a master calendar to link it to a face table of your schema:

Note that the date to link to it I called it PeriodDate as follows:

Date(PeriodDate,'$(DateFormat)') as PeriodDate

now depending on the format of your date in your face table if it is dual

then you have to change the above line to the following:

num(DayStart(PeriodDate)) as PeriodDate

and of course you can change the name to anything to link to your fact table

do not hesitate for more elaboration

Re: How to get data to associate with Calendar

deepakqlikview_123 — Mon, 21 Oct 2013 08:10:42 GMT

Hello Hijazi,

I have used your attached code for making calendar.

It is working fine but I am getting years from 1900 to 2100.

But as per my require ment I need to show only four years 2006,2007,2009,2010,2011,2012,2013.

Can you please tell me how to achieve this.

Thanks,

Deepak.

Re: How to get data to associate with Calendar

ali_hijazi — Mon, 21 Oct 2013 08:16:23 GMT

may you please send me a copy of your document - if it is big reduce it for some value of a column other than date column (year, month, day,...) i'll help you achieve this Waiting for your reply

Re: How to get data to associate with Calendar

deepakqlikview_123 — Mon, 21 Oct 2013 10:02:23 GMT

Hi,

Can you please tell me how can i share my document.

My main code is AS PER BELOW

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

main:

LOAD Sl.No,

     Dealer,

     City,

     State,

     Region,

     [Service Call No.],

     Complaint,

     [Customer Code],

     [Customer Name],

     [Machine Serial No],

     [Material Code],

     [Material Description],

     CallType,

     CallStatus,

     [Creation Date],

     [Creation Time],

     [Closing Date] ,

     [Closing Time],

     [Resolution on Date],

     [Resolution on Time],

     [CRM Call No],

     [CRM Call Date],

     Claimable,

     [Current Location],

     [Current Job Application],

     [Material Handling],

     SCLType,

     Branch,

     ServiceEmployee,

     Type,

     ItemCode,

     Quantity,

     [Claim Type],

     [Parts/Service Price],

     [Parts/Service Value],

     [Parts Reimbursement],

     [Tax Amount],

     [Line Total],

     [Parts From Whs],

     Product,

     Model,

     [Warranty Invoice No.],

     Subject,

     Model1,

     [Item No.],

     [Item Description],

     [Mfr Serial No.],

     [Serial Number],

     F49,

     F50,

     [Time of Call],

     [Line Total1],

     Month,

     Year

FROM

[..\SERVICE CALL PSE_DetailsFinal.xls]

(biff, embedded labels, table is [SERVICE CALL PSE$]);

i HAVE MAKE [Closing date as a primary key in calendar and my main script.]

Please suggest.

Thanks,

Deepak

Re: How to get data to associate with Calendar

ali_hijazi — Mon, 21 Oct 2013 10:19:44 GMT

put num(DayStart( [Closing Date] )) as [Closing Date]

and in the calendar code do the same num(DayStart(PeriodDate)) as [Closing Date]

make sure you set the start year in the first line of the calendar code to 2006

Re: How to get data to associate with Calendar

deepakqlikview_123 — Mon, 21 Oct 2013 10:41:50 GMT

Hi Ali,

Still I am not able to understand,

can you please make desired changes in below calendar code.

LET vDateMin = Num(MakeDate(2010,1,1));

LET vDateMax = Floor(DayEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

//Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,

Date(TempDate,'DD-MM-YYYY' ) AS [Closing Date],

//num(DayStart()) as [Closing Date],

//num(DayStart(PeriodDate)) as [Closing Date],

//Date([Closing Date])as Date,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

num(WeekDay(Today()))as ppp,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'W' & Ceil(Week(TempDate)/8) as Fiscalweek,

Date(Date#('Mar Week 5','MMM WWW D'),'DD/MM/YYYY') AS Sunday,

Year(TempDate) AS CalendarYear,

Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3), Year(TempDate) & Ceil(Month(TempDate)/3)) as YearQtr,

'FY' & Right( Year(TempDate),2) as YearQtrfy,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(day(TempDate)) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

Month(TempDate)&' '& YearName(TempDate,0,2)as FiscalYear,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,

'Sun ' & DayStart(WeekStart(TempDate) + 3) as CalendarSunday

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

//LET vDateMin = Num(MakeDate(2000,1,1));

//

//LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

//

//LET vDateToday = Num(Today());

Thanks,

Re: How to get data to associate with Calendar

ali_hijazi — Mon, 21 Oct 2013 10:52:39 GMT

the calendar code here seems fine

Please send me a copy of your QVW document to see if the format of the dates coming from your data matches that of the calendar