Re: Creating A Master Calendar - Page 5 - Qlik Community

Josh_Good · ‎2012-03-06

This videos show how to create a Master Date Calendar in QlikView. The script mentioned in the video is below.

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 · ‎2012-12-10

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 · ‎2012-12-10

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.

Report Inappropriate Content · ‎2013-05-28

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

Report Inappropriate Content · ‎2013-06-17

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

Report Inappropriate Content · ‎2013-06-17

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

Report Inappropriate Content · ‎2013-06-19

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

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

Report Inappropriate Content · ‎2013-07-29

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 · ‎2013-07-29

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.

Report Inappropriate Content · ‎2013-07-29

Thanks Josh.

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

Josh_Good · ‎2013-07-29

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

Sent from my Commodore 64