How to get data to associate with Calendar

[deepakqlikview_123]

Hello Experts,

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

Thanks in advance.

Regards,

Deepak

[rustyfishbones]

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

[Peter_Cammaert]

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

[ali_hijazi]

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

[deepakqlikview_123]

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.

[ali_hijazi]

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

[deepakqlikview_123]

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

[ali_hijazi]

ok

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

[deepakqlikview_123]

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,

[ali_hijazi]

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