Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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
Highlighted

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

View solution in original post

8 Replies
Highlighted

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

Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted

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

View solution in original post

Highlighted
Creator III
Creator III

That's it.

Highlighted
Creator III
Creator III

Schoolboy error my end

Highlighted
Contributor
Contributor

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!

Highlighted
Partner
Partner

stoked i found this. thank you!