Master Calendar not working

[Anonymous]

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;

[sunny_talwar]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

That's it.

[Anonymous]

Schoolboy error my end

[GDJ100]

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!

[zbeauchemin]

stoked i found this. thank you!