Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create a calendar based on a range of date

Hi everyone

Help me on this...

I would like to create a calendar based on a range of date, like: 07/01/2014, 07/02/2014, 07/03/2014 until 07/31/2014.

Very important: does not matter which list of date we're trying to use, the days always will appear in the correct weekday column...

Sun Mon Tue Wed Thu Fri Sat

                1    2      3    4    5

6       7      8    9    10   11  12

13    14    15  16    17   18  19

20    21    22  23    24   25  26

27    28    29  30    31

I've imported the list of date from the xls file:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

Data:

LOAD weekday([data]) as DiaSemana,

     Day([data]) as Dia

FROM

(ooxml, embedded labels);

5 Replies
Anonymous
Not applicable
Author

Hi,

Check the following article, it explains how to create a master calendar in qlik:

http://www.learnallbi.com/master-calendar-in-qlikview/

Regards,

Not applicable
Author

Hi,

Check this nice website also for the Master calendar creation in the qlikview.

www.qlik4life.com/2014/12/31/master-calendar-creation-in-qlikview/

Harshul

Not applicable
Author

Anonymous
Not applicable
Author

See this blog by HIC

The Master Calendar

You can use below code to create master Calendar.

Let varMinDate = 41640; 

Let varMaxDate = 42735;   

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

 

MasterCalendar: 

Load 

               TempDate AS OrderDate,

               TempDate AS _QA_Start_Date,

               TempDate AS _UAT_Start_date,

               TempDate AS _UAT_Completion_Date,

               TempDate AS _Production_Rollout_date,

               TempDate AS _QA_Completion_Date,

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC;

amit_saini
Master III
Master III

Hi,

Check this.

Thanks,

AS