Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
deepaktibhe
Valued Contributor

How to get data to associate with Calendar


Hello Experts,

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

Thanks in advance.

Regards,

Deepak

9 Replies
Highlighted
rustyfishbones
Honored Contributor II

Re: How to get data to associate with Calendar

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

2013-10-17_0922.png

Highlighted

Re: How to get data to associate with Calendar

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

Highlighted
Partner
Partner

Re: How to get data to associate with Calendar

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

I can walk on water when it freezes
Highlighted
deepaktibhe
Valued Contributor

Re: How to get data to associate with Calendar

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.

Highlighted
Partner
Partner

Re: How to get data to associate with Calendar

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

I can walk on water when it freezes
Highlighted
deepaktibhe
Valued Contributor

Re: How to get data to associate with Calendar

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

Highlighted
Partner
Partner

Re: How to get data to associate with Calendar

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

I can walk on water when it freezes
Highlighted
deepaktibhe
Valued Contributor

Re: How to get data to associate with Calendar

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,

Highlighted
Partner
Partner

Re: How to get data to associate with Calendar

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

I can walk on water when it freezes