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

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

-Josh

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Employee
Employee
Author

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

-Josh

userid128223
Creator
Creator

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
Employee
Employee
Author

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,

....

Not applicable

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];

Not applicable

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
Employee
Employee
Author

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

Not applicable

I fixed it with

SQL SELECT *

FROM `Grand Rapids Pick Detail 2011`

UNION ALL

SELECT *

FROM `Grand Rapids Pick Detail 2012`;

Thanks

qlikconsultant
Creator III
Creator III

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