topic Re: Master Calendar not working in QlikView

Master Calendar not working

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Master Calendar not working

sunny_talwar — Thu, 06 Oct 2016 12:54:45 GMT

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

sunny_talwar — Thu, 06 Oct 2016 12:56:18 GMT

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

Re: Master Calendar not working

Anonymous — Thu, 06 Oct 2016 12:57:22 GMT

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

Re: Master Calendar not working

sunny_talwar — Thu, 06 Oct 2016 12:59:32 GMT

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

Re: Master Calendar not working

Anonymous — Thu, 06 Oct 2016 13:04:12 GMT

That's it.

Re: Master Calendar not working

Anonymous — Thu, 06 Oct 2016 13:05:30 GMT

Schoolboy error my end

Re: Master Calendar not working

GDJ100 — Mon, 15 Jul 2019 13:01:33 GMT

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!

Re: Master Calendar not working

zbeauchemin — Thu, 15 Aug 2019 14:34:08 GMT

stoked i found this. thank you!