Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

lakwinder
Contributor III

Master Calendar not working

Hi Guys,

I'm using the master calendar below and for some odd reason the date fields from the MC are not working, the date from my table and the MC table link, but the data is not filtering in months.

I've used this MC many times before without problems.

Orders:

Date(OrderDate) as OrderDate

//////////////////////// how the date field is

Regards

****************************************************************************

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;

1 Solution

Accepted Solutions

Re: Master Calendar not working

Does you date in Orders table include time component for any reason? If they do, then the connection won't occur unless you use Floor statement in the Orders table:

Orders:

Date(Floor(OrderDate)) as OrderDate

8 Replies

Re: Master Calendar not working

Have you checked if these variables are working fine?

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

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


May be add a trace statement to check them

TRACE $(varMinDate);

TRACE $(varMaxDate);

Re: Master Calendar not working

Important thing here is to make sure that your OrderDate is correctly interpreted as date by QlikView

Why don’t my dates work?

Get the Dates Right

lakwinder
Contributor III

Re: Master Calendar not working

That's the first thing I did and it's a date.

Re: Master Calendar not working

Does you date in Orders table include time component for any reason? If they do, then the connection won't occur unless you use Floor statement in the Orders table:

Orders:

Date(Floor(OrderDate)) as OrderDate

lakwinder
Contributor III

Re: Master Calendar not working

That's it.

lakwinder
Contributor III

Re: Master Calendar not working

Schoolboy error my end

GDJ100
New Contributor

Re: Master Calendar not working

Very useful tip! Had the same issue but the source of data changed for me and was pulling in the timestamp too. This massively helped!

Partner
Partner

Re: Master Calendar not working

stoked i found this. thank you!