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: 
Josh_Good
Employee
Employee

Creating A Master Calendar

This videos show how to create a Master Date Calendar in QlikView.  The script mentioned in the video is below.

http://youtu.be/ScdIQvWzVFs

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

               min(OrderDate) as minDate,

               max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

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,

               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;

Drop Table TempCalendar;

78 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you post yout script?

simongoodman
Creator
Creator

Rob below is the calendar script where it is failing. I also attached the

Error screenshot.

Simon

//*****Fiscal Year Quarter Map*****

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q2

2, Q2

3, Q2

4, Q3

5, Q3

6, Q3

7, Q4

8, Q4

9, Q4

10, Q1

11, Q1

12, Q1

];

LET varMinDate = Num(MakeDate(2010,10,1)); //

first date

LET varMaxDate = Floor(num(Today())); // will find

the last record

LET varToday = Num(today());

// Finds todays date

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate) + rowno() - 1 AS Num,

date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE 1

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

//*************** Master Calendar ***************

MasterCalendar:

LOAD

TempDate AS PostingDate,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

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

Quarter,

yearname ( TempDate, 0, 10 ) as FiscalYear,

Date(monthstart(TempDate), 'MMM-YYYY') AS

MonthYear,

Month(TempDate)&'-'&right(year(TempDate),10) as

FiscalMonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,

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

RC12,

inyeartodate(TempDate, $(varToday), 0 [,

first_month_of_year = 10]) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(varToday), -1 [,

first_month_of_year = 10]) * -1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

On Wed, Jun 13, 2012 at 6:06 AM, Rob Wunderlich <

Not applicable

This line you have a double comma

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,,

simongoodman
Creator
Creator

This is a typo and not in the script.

On Wed, Jun 13, 2012 at 11:39 AM, Felim Shanaghy <

Not applicable

The code below works, its related to : -

inyeartodate(TempDate, $(varToday), 0 [,

first_month_of_year = 10]) * -1   AS CurYTDFlag,

                          inyeartodate(TempDate, $(varToday), -1 [,

first_month_of_year = 10]) * -1 AS LastYTDFlag

The above are incorrectly formatted

//*****Fiscal Year Quarter Map*****

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q2

2, Q2

3, Q2

4, Q3

5, Q3

6, Q3

7, Q4

8, Q4

9, Q4

10, Q1

11, Q1

12, Q1

];

LET varMinDate = Num(MakeDate(2010,10,1));                  //first date

LET varMaxDate = Floor(num(Today()));                       // will find the last record

LET varToday = Num(today());                                                                                // Finds todays date

//*************** Temporary Calendar ***************

TempCalendar:

LOAD $(varMinDate) + rowno() - 1 AS Num,

     date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE 1

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

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS PostingDate,

     Week(TempDate) AS Week,

     Year(TempDate) AS Year,

     Month(TempDate) AS Month,

           Day(TempDate) AS Day,

     Weekday(TempDate) AS WeekDay,

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

     yearname ( TempDate, 0, 10 ) as FiscalYear,

     Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     Month(TempDate)&'-'&right(year(TempDate),10) as FiscalMonthYear,

     Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

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

     inyeartodate(TempDate, $(varToday), 0, 10) * -1   AS CurYTDFlag,

     inyeartodate(TempDate, $(varToday), -1, 10) * -1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

simongoodman
Creator
Creator

Hi Felim, That makes sense and works. Any idea how can I create a fiscal

week, or match week to fiscal month?

On Wed, Jun 13, 2012 at 12:06 PM, Felim Shanaghy <

Not applicable

Hi Simon is I am uncertain how your fiscal periods work, in the past I have used calendars stored in spreadsheets dictating the calendar, the reason I done this at the time was that the fiscal week would actually change, so lets say if your fiscal week 1 starts on the first monday of October, we don't generally know what day that is.  I am sure we could do it in code if you give us the business rules for the fiscal periods

jaime_aguilar
Partner - Contributor III
Partner - Contributor III

For some reason when I use the loop: AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); it does not show some months in the correct order whenever I select them. To solve that I used the next line instead of the while loop:

$(varMaxDate) - $(varMinDate) + 1;

Anyway, this is such a good Calendar example

userid128223
Creator
Creator

Master Calander using 2 dates

Attach I have qvw that has inline table containing 2 different dates.

  1. I want to create one master calander with combination of both dates.
  2. Master Calander should have connection with inline table so that when i create month, Year selector, I can show corresponding data.

I have dealt with creating master calander from one date, however creating from 2 dates is confusing me.

example qvw can be found below.

http://community.qlik.com/message/242188#242188

Not applicable

Hey Josh, I just noticed a bug with the script. If a week goes over into the new year it gets the incorrect year/week assigned.

Take for example Jan 1, 2012

in your code Week(TempDate) & '-' & Year(TempDate) as WeekYear,

The week returns 52, because it is the 52nd week of 2011, week 1 of 2012 starts on the following Monday. The year argument returns 2012. so the end result is "52-2012" which is really the end of 2012 instead of 2011.

To get around this I use the weekstart argument for the weeks so it always looks at the Monday of the week.

e.g. Week(weekstart(TempDate)) & '-' & Year(weekstart(TempDate)) as WeekYear,

Cheers,