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

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

qlikconsultant
Creator III
Creator III

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.

Not applicable

Just try these.

load * inline [

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....

Not applicable

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

Not applicable

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

Not applicable

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

asd.JPG

It continues loading and in 45 imillon fails..

This is the code

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

               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:

LOAD

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

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

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

MasterCalendarPedidos:

Load

               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

Not applicable

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

Josh_Good
Employee
Employee
Author

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.

Not applicable

Thanks Josh.

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

Josh_Good
Employee
Employee
Author

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

Sent from my Commodore 64