69 Replies Latest reply: Feb 17, 2015 10:22 AM by Oliver Williams

# 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:
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
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:
\$(varMinDate) + Iterno()-1 As Num,
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:
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;
```
• ###### Creating A Master Calendar

Thanks! For anyone looking to learn the fundamentals of using a master calendar, I would definitely recommend watching this video.

• ###### Re: Creating A Master Calendar

Josh, I've tried to implement the Master Calendar after having watched your video, and I feel like I understand how everything works, but I'm getting an error on reload with the temp table/autogenerate. Any idea why that would be?

Here is my error:

TempCalendar:

+ Iterno()-1 As Num,

Date( + IterNo() - 1) as TempDate

AutoGenerate 1 While  + IterNo() -1 <=

• ###### Re: Creating A Master Calendar

It looks like you are not generating values for varMinDate (and like also for varMaxDate) for some reason.  Try using the degubber and running the script using 'Step' to confirm this (see image which show what you should be seeing if you are generating values).  The reason for this could either be your Temp table is empty or there is some sort of syntax error on the line that sets the variable varMinDate (line 37 of the image below).

• ###### Re: Creating A Master Calendar

That's what I thought too Josh, so here is what I did.

To try and debug, I removed everything but the below:

QuartersMap:
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
min(PerformanceDate) as minDate,
max(PerformanceDate) as maxDate
Resident DimPerformanceDate;

When I run the above, and display mindate and maxdate in a listbox it definitely contains values.

But once it gets to that autogenerate line it can't find them...

Also, another error I see in debugger is that monthstart must contain two values. (Not sure)

Does your script only work on a certain version? Because these problems are really strange.

I'm running QV9. Figured I'd throw that out there before I keep trying to troubleshoot something that may not work.

-Anthony

• ###### Re: Creating A Master Calendar

Hi Anthony,

Yes the script should work in V9.  I origionally started using in V9 myself.  I'm a bit at a lost as to why you are running into an error.  The only thing I can think of is that your data is not being recongized as dates by QlikView.  Maybe see if you can get the script to work with different data (to rule that out).

Regards,

Josh

• ###### Creating A Master Calendar

Hi Josh

q1 = jan to march

q2= apr to jun

what if the quarter starts from dec.

q1=dec - feb

q2= mar - may

how do you make changes to master calander to accomodate that.

thanks

• ###### Creating A Master Calendar

We are all coding MasterCalendars over and over again. It's an interesting excercise to learn scripting, but if you just want to get the calendar done, consider using Qlikview Components http://qlikviewcomponents.org, the free open source QV script library.

http://qlikviewnotes.blogspot.com/2012/01/easy-period-analysis-using-qlikview.html

-Rob

• ###### Re: Creating A Master Calendar

'Q' & Ceil (month(AddMonths(Tempdate,1)/3) as Quarter

-Rob

http://robwunderlich.com

• ###### Re: Creating A Master Calendar

Thanks Josh. I tried your script it gave me error.

"Generic tables must contain at least 3 fields"

Rob.

I have tried using calander from QV components. However how do you adjust quarter to start at different month using components.

thanks

• ###### Re: Creating A Master Calendar

Sorry about that.  I failed to name the second column in the inline load.   I have corrected it in my previous post.

-Josh

The bold is what was added:

QuartersMap:

Month, Q

1, Q1

• ###### Re: Creating A Master Calendar

Thanks Josh.

It works. however when i am getting weird dates.

19981203    (YYYYMMDD)   Source

however when i bring that date and try passing thru date function, i get weird numbers.

year(saledate)as year,

I get numbers that is unlike year.

56606

Due to this my master calander function is not working properly.

• ###### Re: Creating A Master Calendar

I believe the issue is your dates are not in a format that QlikView is expecting you can either change the default format or change the date to a format QlikView is expecting.

To change the default format you need to change the statement that sets this.  This is typicaly line 7 on the first tab of the script.

e.g. change  SET DateFormat='M/D/YYYY';  to SET DateFormat='YYYYMMDD';

Alternatively (my preference), change the format of the source data by parsing out the date into its components and putting it back together when you load it.  The expression would look something like this:

MakeDate(Left(SourceDate, 4), Right(Left(SourceDate,2),6), Right(SourceDate,2)) as Date

• ###### Creating A Master Calendar

Thanks Josh & Rob for your help.

• ###### Re: Creating A Master Calendar

I have just seen this and I am in the process of creating a fiscal calendar.

How did you get CurYTDFlag and LastYTDFlag to start at the beginning of the fiscal year? I have tried a couple of things unsucessfully.

Similarly how did you get Week to realign with the fiscal months? Or how did you create a fiscal week in the script?

• ###### Re: Creating A Master Calendar

Creating fiscal fields is all about using the FirstMonthOfYear parm in the various date functions. For example, see the help for InYearTodate().

-Rob

• ###### Re: Creating A Master Calendar

Hi Rob

Thanks for that. I understand the concept but failed in execution. I think

I can not see the wood for the trees. 1 is as per Help and 2 is one of my

failed  efforts.

1. inyeartodate(date, basedate , shift )

2. inyeartodate(TempDate, \$(varToday), 0 )

• -1 AS CurYTDFlag

Simon

On Tue, Jun 12, 2012 at 3:17 PM, Rob Wunderlich <

• ###### Re: Creating A Master Calendar

Can you post yout script?

• ###### Re: Creating A Master Calendar

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

Error screenshot.

Simon

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

QuartersMap:

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:

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

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

AUTOGENERATE 1

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

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

MasterCalendar:

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 <

• ###### Re: Creating A Master Calendar

This line you have a double comma

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

• ###### Re: Creating A Master Calendar

This is a typo and not in the script.

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

• ###### Re: Creating A Master Calendar

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:

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:

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;

• ###### Re: Creating A Master Calendar

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 <

• ###### Re: Creating A Master Calendar

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

• ###### Re: Creating A Master Calendar

Hi all,

I've used this master calendar script many times, in other apps, without any issues.

But in my attached example, I seem to be getting the 'Field <<> not found' error.

It's definitely the formatting, as I've Stepped through the script in the debugger.

I've tried formatting the date field in different ways.

Any ideas?

Cheers

• ###### Re: Creating A Master Calendar

To me it looks like the field you are using to create the calendar (CALL_DATETIME) is a text field.  In your script you have converted to a date (CALL_DATETIMEF).  I believe if you use that field in teh calendar script then it will work.

• ###### Re: Creating A Master Calendar

Thanks Josh.

I was dumb enough to forget I had created that date converted field.

• ###### Re: Creating A Master Calendar

No problem.  Been there. If I had a dime every time, I'd be very rich.

Sent from my Commodore 64

• ###### Re: Creating A Master Calendar

If you want to offset your entire Calendar -- a fiscal calendar -- set the FirstMonth parm to 12 in your call to Qvc.Calendar or Qvc.CalendarFromField. For example:

CALL Qvc.CalendarFromField('OrderDate', 'Fiscal Calendar', 'Fiscal ', '12');

If instead you want a standard calendar, but want to offset just the Quarter field, use the new code extension feature available in Qvc 4.0.

SET Qvc.Global.Extension.Directory=C:\local\QvcExtensions;

and the contents of C:\local\QvcExtensions\CalendarExtFields.qvs would be:

,'Q' & Ceil (month(AddMonths(Date,1)/3) as [\$(_fieldPrefix)OffsetQuarter]

If you want to replace the standard Quarter field with the offset one, follow the calendar generation with:

DROP FIELD Quarter;

RENAME FIELD OffsetQuarter TO Quarter;

-Rob

• ###### Re: Creating A Master Calendar

Hi Rob,

It would be very helpful if you could please explain the following.

I am trying to use a Fiscal year calendar where the year starts iN December andnI have used the following for Current YTD and Prior YTD.

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

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

The only problem is that as of today my data is only for YTD end of May. When it calculates the Prior Year it counts data al lthe way to today's date last year. How do i make the above script to stop at end of previous month - which in this case is May 2013. I just want the above to calculate the YTD from Dec 2012 to May 2013 and Dec 2011 to may 2012.

Thanks,

Harsha

• ###### Re: Creating A Master Calendar

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:

1,Q1

2,Q1

3,Q1

4,Q2

5,Q2

6,Q2

7,Q3

8,Q3

9,Q3

10,Q4

11,Q4

12,Q4];

• ###### Re: Creating A Master Calendar

Just try these.

Date(orderdate) as Date,

Month(orderdate) as Month,

Year(orderdate) as Year,

if(num(Month(orderdate)) >=4 and num(Month(orderdate))<=6, 'Q1',

if(num(Month(orderdate)) >=7 and num(Month(orderdate))<=9, 'Q2',

if(num(Month(orderdate)) >=10 and num(Month(orderdate))<=12, 'Q3',

if(num(Month(orderdate)) >=1 and num(Month(orderdate))<=3, 'Q4')))) as Quarter,

];

For output of this script, please open the attachmnet.

Still having any doubts please let me know....

• ###### Re: Creating A Master Calendar

Just a quick note;

I have spent good deal of time to understand why my master calendar is not working and the error I got was the error you mentioned above.

The script is absolutely ok, it is just that the because of complexity of my data, I am using "QUALIFY" statement previously in the script and in order to make master calendar work you either have to adress that and correct references to exact and full field names or, just in front of your master calendar script add "UNQUALIFY *;"

I hope this helps, I spent too much time figuring this out and I am surprised that nobody else mentioned this rookie mistake.

• ###### Re: Creating A Master Calendar

Hi,

To do this you will need to change the Mapping table to match your quarters.  I would either drive it off fiscal year definitions from a data source or using an inline load. The inline load would look like this:

QuartersMap:

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

You may also want to define a Fiscal Year as well.  In the load statement that defined the Master Calendar table you could add a line something like this:

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

Putting it all together your script would look something like below.

I hope that helps!

QuartersMap:

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

Temp:

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:

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

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

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

MasterCalendar:

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,

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

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

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

• ###### Creating A Master Calendar

Can you please explain the below statement

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

thanks

• ###### Re: Creating A Master Calendar

Hi Josh,

It would be very helpful if you could please explain the following. I am trying to use a Fiscal year calendar where the year starts iN December andnI have used the following for Current YTD and Prior YTD.

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

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

The only problem is that as of today my data is only for YTD end of May. When it calculates the Prior Year it counts data al lthe way to today's date last year. How do i make the above script to stop at end of previous month - which in this case is May 2013.

I just want the above to calculate the YTD from Dec 2012 to May 2013 and Dec 2011 to may 2012.

Thanks,

Harsha

• ###### Re: Creating A Master Calendar

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

• ###### Re: Creating A Master Calendar

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

• ###### Re: Creating A Master Calendar

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,

• ###### Re: Creating A Master Calendar

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

-Josh

• ###### Re: Creating A Master Calendar

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

WeekYear(tempdate)

will return 2011.

-Rob

• ###### Re: Creating A Master Calendar

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

-Josh

• ###### Re: Creating A Master Calendar

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:

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,

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

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

• ###### Re: Creating A Master Calendar

My recommendation is to use the AddMonth Function.

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,`

....

• ###### Re: Creating A Master Calendar

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

Material,

`Pallet_ID`,

`Picked Cases`,

PickType,

`Plant_NO`,

Route;

SQL SELECT *

FROM ` Pick Detail 2011`;

Material,

`Pallet_ID`,

`Picked Cases`,

PickType,

`Plant_NO`,

Route;

SQL SELECT *

FROM `Pick Detail 2012`;

• ###### Re: Creating A Master Calendar

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

• ###### Re: Creating A Master Calendar

I fixed it with

SQL SELECT *

FROM `Grand Rapids Pick Detail 2011`

UNION ALL

SELECT *

FROM `Grand Rapids Pick Detail 2012`;

Thanks

• ###### Re: Creating A Master Calendar

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

• ###### Re: Creating A Master Calendar

Hi,

Have you taken a look at this posting:  http://community.qlik.com/thread/24869

This post show how to create a datetime calendar.

One thing you may want to consider is to separate the dates from the time and create separate date and time tables.  This will reduce the number of unique values you will required to generate.

-Josh

• ###### Re: Creating A Master Calendar

Hi Josh,

thanks for the tip. I think i missed this interesting post.

Unfortunly i need calendar with unique values for every minute of an month so I have to generate a lots of values.

My goal is to compare data from 2 different sources which connected only by time.

Thanks.

• ###### Re: Creating A Master Calendar

Hi everybody, Im using QLIKVIEW 11

I saw this thread and i tried to use this code in my script, but after loading a lot of data, script "failed" without error.

Its not a RAM problem because i also run it in a 8GM machine and same result.

¿Anyone can help me with this? I don't know where is the problem..

My table has 1.000.000 of registers... but this TempCalendar loads like 40 millon..and there is the problem i think

Format date is 2009-11-26 12:21:00.000 in SQL

But in qlikview appears like  26/11/2009 12:21:00 AM

here is an image

This is the code

QuartersMap:

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

min(Pedido_Fecha) as minDate,

max(Pedido_Fecha) as maxDate

Resident Pedidos;

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

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

DROP Table Temp;

TempCalendar:

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

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

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

MasterCalendarPedidos:

TempDate AS Pedido_Fecha,

week(TempDate) As PedidoWeek,

Year(TempDate) As PedidoYear,

Month(TempDate) As PedidoMonth,

Day(TempDate) As PedidoDay,

YeartoDate(TempDate)*-1 as PedidoCurYTDFlag,

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

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

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

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

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

WeekDay(TempDate) as PedidoWeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Thanks

• ###### Re: Creating A Master Calendar

I am working with a customer where their Q1 is Oct/Nov/Dec… the really nice master calendar above returns Q1 = Jan/Feb/March... what would I have to do to have Q1 = Oct/Nov/Dec and then Q2 = Jan/Feb/Mar etc etc.. I am using 11.2 btw.

I messed around with the scrip  a bunch to see if I could make it happen... no luck so far and am crunched for time.

Tx!

k

• ###### Re: Creating A Master Calendar

You can use the AddMonths function to move the calendar date forward or backwards to be the correct fiscal date.  In your case you will want to add three months to the date so Oct 1st moves to the first day of the year (Jan 1st) from there you can apply the vaious funcitons to make your calendar.  Note you can keep both fiscal and calendar dates in the same table.

So for FiscalQuarter it would look like this....

MasterCalendar:

...

...

Resident TempCalendar

• ###### Re: Creating A Master Calendar

Hey Josh,

I'm a newbie. The original script you shared is not working for me. It gives the following errors:

TempCalendar:

3)Error in expression: MonthStart takes 1-2 parameters

MasterCalendar:

• ###### Re: Creating A Master Calendar

Hi Josh,

Love the calendar and have been using it with no issues for a while now.

But,

I have a new app where I need to show sum of a measure from last week.

Using Max(Week) is giving me "52" (obviously from 2013), but what I need to see is week 3 from 2014.

Any ideas for me ?

I'm trying to show the most active customer last week basically.

Thanks

T

• ###### Re: Creating A Master Calendar

Tony,

I would setup a flag in the calendar that flags all dates that are 'last week'.  First define what today is with a variable and then check and see the last week dates.  so something like this:

Let vToday = Today(0);

///A Bunch of Script

///The Beginning of the Calendar Script...

MasterCalendar:

TempDate AS OrderDate,

//....

if (WeekStart(TempDate) = WeekStart(\$(vToday)-7), 1) as LastWeekFlag

//...

Resident TempCalendar

//The Rest of the Calendar Script

Hope that helps,

Josh

PS I like your Mr. T avatar. "I pity the fool who doesn't use QlikView!"

• ###### Re: Creating A Master Calendar

Thank you so much Josh,

I am not sure if I am doing the pitying or being the fool lately!

• ###### Re: Creating A Master Calendar

Hi Josh,

Am new with Qlikview and have little problem, hope i find help here.

So, i created a MasterCalendar liked showed in the tutorial. (Thx by the way for this)

Then i created two variables FromDate and ToDate but when i click on the Calendar I only see 1 Month instead of all the dates shows in the preview of the MasterCalendar. I would like to show all months and days from my Calender, how can i do this?

The two variables have as default value 'Varmaxdate' from the Mastercalendar script.

MJ

• ###### Re: Creating A Master Calendar

Hi MJ,

Great to hear you found this video helpful.  Have you seen my posting on selecting arbitrary date ranges?

Selecting Arbitrary Date Ranges

I think it is exactly what you are looking for

-Josh

• ###### Re: Creating A Master Calendar

Hi Josh,

thanks a lot, that one was perfectly what i needed

Again Thanks and wish u a very nice afternoon from Hamburg

Bye

• ###### Re: Creating A Master Calendar

Hi Josh,

This is awesome for the fact data.

How do you suggest we use dates for the forecast calendar?

Thx

G

• ###### Re: Re: Creating A Master Calendar

Hi Gregor,

I'm not sure what is different about your 'forecast calendar'.  This is not reason you can't have multiple date fields (and hence multiple calendars) in the same data model.  If you do that then you will want to name each calendar and the fields in it appropriately.  So you could have a "Forecast Calendar" and "Shipped Calendar" etc.

To do this you would repeat the script for each calendar and adjust the field names as appropriate - this is the easiest way.

A more elegant way would be to use the script below to loop through each calendar you want to create and drive which calendar is created from the inline table (or an external table).  I find this approach to be a bit 'touchy' and often requires a bit of trouble shooting to get it to work just right.

-Josh

Qlik

CalendarNames:

CalendarName, Table, CalendarNameSpaces, CalendarNameNoSpaces,

ActualEnd, Data, Actual End, Actual End

EstimatedEnd, Data, Estimated End, Estimated End

Start, Data, Start, Start,

];

QuartersMap:

rowno() as Month,

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

AUTOGENERATE (12);

For i = 0 to (NoOfRows('CalendarNames')-1);

Let varCalendarNameNoSpaces = Peek('CalendarName', \$(i), 'CalendarNames');

Let varCalendarNameSpaces = Peek('CalendarNameSpaces', \$(i), 'CalendarNames');

Let varCalendarFromTable = Peek('Table', \$(i), 'CalendarNames');

Set varDateField = \$(varCalendarNameSpaces) Date;

Temp:

min([\$(varDateField)]) as minDate,

max([\$(varDateField)]) as maxDate

Resident \$(varCalendarFromTable);

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

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

DROP Table Temp;

TempCalendar:

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

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

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

\$(varCalendarNameNoSpaces)Calendar:

TempDate AS [\$(varCalendarNameSpaces) Date],

week(TempDate) As [\$(varCalendarNameSpaces) Week],

Year(TempDate) As [\$(varCalendarNameSpaces) Year],

Month(TempDate) As [\$(varCalendarNameSpaces) Month],

Day(TempDate) As [\$(varCalendarNameSpaces) 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 [\$(varCalendarNameSpaces) Month-Year],

ApplyMap('QuartersMap', month(TempDate), Null()) as [\$(varCalendarNameSpaces) Quarter],

Week(TempDate) & '-' & Year(TempDate) as [\$(varCalendarNameSpaces) Week-Year],

WeekDay(TempDate) as [\$(varCalendarNameSpaces) Week-Day]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

NEXT;

Drop Table CalendarNames;

• ###### Re: Creating A Master Calendar

Hi Josh.

Is there a way you could provide script for QuarterYear? You have MonthYear and WeekYear? I have tried but to no avail.

Thanks so much.

Barb

• ###### Re: Creating A Master Calendar

Hi Barb,

For QuarterYear this should work:

ApplyMap('QuartersMap', month(TempDate), Null()) & '-' & Year(TempDate) as QuarterYear,

this will produce a text string so it may not natively sort correctly.  Use Sorty by Expression with the expression

MIn(Date)

-Josh

Qlik

• ###### Re: Creating A Master Calendar

It worked beautifully. Thanks again, Josh, for your help. You are so fast and so knowledgeable:) Have a nice weekend.

• ###### Re: Creating A Master Calendar

Hi,

another possibility without sorting issues could be:

Dual('Q'&Ceil(Month(TempDate)/3)&'-'&Year(TempDate),QuarterStart(TempDate)) as QuarterYear

without the need for a Quarter mapping table.

hope this helps

regards

Marco

• ###### Re: Creating A Master Calendar

Hi all,

Very interesting topic even  for a newbie. I have tried it and works perfectly. I have a question though. Based on the original post, which expression should I use in set analysis to get YTD and LYTD ? I imagine LastYTDFlag and CurYTDFlag ?

Thank you

• ###### Re: Creating A Master Calendar

Hi Renos,

The the flags in the script result in a 1 or 0 so you can simply multiple the field of interest by that appropriate flag  Something like this:

Sum(Sales * LastYTDFlag)

All records that have not been flagged as LastYTD will become zero.

-Josh

Qlik

• ###### Re: Creating A Master Calendar

I would think the set analysis option of

Sum({<LastYTDFlag={1}>} Sales) would perform better would it not, instead of performing the sum across the fact & calendar tables (plus the whole dataset) like that?

• ###### Re: Creating A Master Calendar

Thanks. This is a really interesting piece. I was wondering if it is possible to expand on this so that you could look at an invoice period using the start and end dates. My particular interest would be splitting it by how many days are in a month. i.e.from

To:

I have tried multiple adaptations to no effect.