Creating A Master Calendar

[Josh_Good]

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;

[rwunderlich]

Can you post yout script?

[simongoodman]

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 <

[#4]

This line you have a double comma

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

[simongoodman]

This is a typo and not in the script.

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

[#6]

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]

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 <

[#8]

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]

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]

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

[#11]

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,