topic Re: Master Calendar in QlikView

Master Calendar

slribeiro — Tue, 03 Nov 2015 12:39:55 GMT

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

TempCalendarRange:

LOAD

date('01/01/2014') as MinDate,

date('31/12/2016') as MaxDate

Resident MainTable;

Let vMinDate = Num(Peek('MinDate', 0, 'TempCalendarRange'));

Let vMaxDate = Num(Peek('MaxDate', 0, 'TempCalendarRange'));

DROP Table TempCalendarRange;

TempCalendar:

LOAD Distinct

$(vMinDate) + IterNo()-1 as Num,

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

AutoGenerate (1)

While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:

LOAD

TempDate,

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;

It's giving an error as if the variable vMaxDate is not created!

Can anyone see something wrong with this script?

Regards

Simão Ribeiro

Re: Master Calendar

slribeiro — Tue, 03 Nov 2015 14:27:22 GMT

This is the error that appears to me...

Help me please

Re: Master Calendar

swuehl — Tue, 03 Nov 2015 16:10:43 GMT

What is your default date format? 'MM/DD/YYYY' or 'DD/MM/YYYY'? Only one is matching the MaxDate...

Re: Master Calendar

swuehl — Tue, 03 Nov 2015 16:23:07 GMT

Both variables do not seem set correctly, so you need to first correct for this.

Maybe start with

/* commented out

TempCalendarRange:

LOAD

date('01/01/2014') as MinDate,

date('31/12/2016') as MaxDate

Resident MainTable;

Let vMinDate = Num(Makedate(2014,1,1) );

Let vMaxDate = Num(Makedate(2016,12,31) );

//DROP Table TempCalendarRange;

Re: Master Calendar

ElizaF — Tue, 03 Nov 2015 19:33:14 GMT

Depending on how you configured is variable DateFormat at the beginning of the script.

SET DateFormat='DD/MM/YYYY';

If not configured, then it is the Date Format of the regional setting