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;

[Josh_Good]

Thanks Iain!  I have updated the script in the original post.

-Josh

[rwunderlich]

I think a better solution may be to use the WeekYear() function.

WeekYear(tempdate)

will return 2011.

I only know about this because I made the same mistake in the Qlikview Components calendar and someone sent me a correction.

-Rob

[Josh_Good]

Thanks Rob.  Sometimes I'm amazed at the number of functions we have to in QV!  I have updated the script.

-Josh

[userid128223]

Hi Rob

So in my case I am doing master calander where fiscal starts from sep to Aug. I have alligned quarters accordingly for inline. How do I make changes to fiscal year.

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q2

2, Q2

3, Q3

4, Q3

5, Q3

6, Q4

7, Q4

8, Q4

9, Q1

10, Q1

11, Q1

12, Q2

];

What should be the change to below code:

If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

Should it be adjusted to.below?

If (Month(TempDate) = 12, Year(TempDate) + 8, Year(TempDate)) as FiscalYear,

Do I need to make any other change for my Calander to function.

[Josh_Good]

My recommendation is to use the AddMonth Function.

Year(AddMonth(TempDate,3)) as FiscalYear

This will take a date in Sept 2012 and return 2013.  If you wish you can add this line to the script for the MasterCalendar table and retain the line that just has the calendar year so you will be able to filter on Fiscal Year and/or Calendar Year.

You may also want to add line for the fiscal month number but I would also retain the calendar month.  This will enable you to sort or the Month field in the correct fiscal order (i.e. sept first)

....

Month(TempDate) As Month,

Num(Month(AddMonth(TempDate,3))) as FiscalMonthNumber,

....

[#7]

You can use below code, for getting quarters with differant months.

We can change order of months and quarters in the way we needed.

Mapinline:

mapping Load * inline [month,Quarter

1,Q1

2,Q1

3,Q1

4,Q2

5,Q2

6,Q2

7,Q3

8,Q3

9,Q3

10,Q4

11,Q4

12,Q4];

[#8]

Hi I am truing to implement the master calendar but I have to load data from two tables in an access file one containing dates from 2011 and the other from 2012.

I use the following code, but I only get the 2011 data to apear, what should I change?

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\t42386\Documents\GR Data\ Pick Detail 2011-12.accdb];

OrderHeader:

LOAD Date,

    Material,

    `Pallet_ID`,

    `Picked Cases`,

    PickType,

    `Plant_NO`,

    Route;

SQL SELECT *

FROM ` Pick Detail 2011`;

OrderHeader:

LOAD Date,

    Material,

    `Pallet_ID`,

    `Picked Cases`,

    PickType,

    `Plant_NO`,

    Route;

SQL SELECT *

FROM `Pick Detail 2012`;

[Josh_Good]

Hi,

If you load two tables into QV that have exactly the same fields in them, then QV will automatically concatenate the tables together. The 2012 and the 2011 data will be in the same table. From there you should be good to follow the example.

-Josh

Sent from my Commodore 64

[#10]

I fixed it with

SQL SELECT *

FROM `Grand Rapids Pick Detail 2011`

UNION ALL

SELECT *

FROM `Grand Rapids Pick Detail 2012`;

Thanks

[qlikconsultant]

Nice Calendar, but I have a little bit of troubel to configure the skript to make the calendar minute based.