Issues with Master Calendar

[#1]

I have just completed the designer course and was opening the file created on the Personal Edition back at the office in order to demonstrate to my colleagues what Qlikview can offer us.  However it has partially broken as regards the master calendar and is turning all month, quarter and year variable into NULL values except for the first value (1/1/2013).

This was working OK on the course machine, but not when opened on my PC.

let varMinDate = num(peek('OrderDate',0,'Facts'));

let varMaxDate = num(peek('OrderDate',-1,'Facts'));

TempCalendar:

Load

date($(varMinDate) + rowno() -1) as TempDate

autogenerate $(varMaxDate) - $(varMinDate) +1;

MasterCalendar:

LOAD

  date(TempDate) AS OrderDate,

  week(TempDate) AS Week,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Day(TempDate) AS Day,

  inyeartodate (TempDate,'2016-03-31', 0) *-1 as CYTDFlag,

  inyeartodate (TempDate,'2015-03-31',0) *-1 as LYTDFlag,

  date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

  'Q' & ceil(month(TempDate)/3) as Quarter,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

  weekday(TempDate) AS WeekDay,

  if(TempDate>='3/1/2016', Dual('30 Days', 1),

                 If(TempDate >= '1/30/2016' and TempDate < '3/1/2016', Dual('31 - 60 Days', 2),

                          If(TempDate >= '12/31/2015' and TempDate < '1/30/2016', Dual('60 - 90 Days', 3),

                                   Dual('90+ Days', 4)))) as OrderFrequency

RESIDENT

  TempCalendar

ORDER BY

  TempDate Asc;

DROP TABLE TempCalendar;

[Chanty4u]

jst chk dis

Creating A Master Calendar

[sunny_talwar]

Do you have any other date in your database? other than 1/1/2013? And are those dates read correctly (Why don’t my dates work?)

[#4]

There is three years worth of data and the orders dates themselves are being read correctly as dates (checked that other link previously) - the issue seems to be on converting the dates to quarters, months & years.

[its_anandrjs · Accepted solution]

Hi,

Check your Fact table for Orderdate field for correct field or the date field format also in the SET function

Here is your complete test code which is running well

///////////////////////////////////////////////////////////

SET DateFormat='DD-MM-YYYY';  ////////This is important line

let varMinDate = num('01-01-2013');
let varMaxDate = num(Today());

TempCalendar:
Load
date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) + 1 ;


MasterCalendar:
LOAD
date(TempDate) AS OrderDate,
week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
inyeartodate (TempDate,'2016-03-31', 0) *-1 as CYTDFlag,
inyeartodate (TempDate,'2015-03-31',0) *-1 as LYTDFlag,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
'Q' & ceil(month(TempDate)/3) as Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay,
if(TempDate>='3/1/2016', Dual('30 Days', 1),
If(TempDate >= '1/30/2016' and TempDate < '3/1/2016', Dual('31 - 60 Days', 2),
If(TempDate >= '12/31/2015' and TempDate < '1/30/2016', Dual('60 - 90 Days', 3),
Dual('90+ Days', 4)))) as OrderFrequency
RESIDENT TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

///////////////////////////////////////////////////

I use here Min date as =num('01-01-2013'); and

Max date as today's date =num(Today())

Check this code

Regards,

Anand

[sunny_talwar]

I don't see anything wrong in there would you be able to share your app to look at the code?

[its_anandrjs]

If this is (1/1/2013) your date format then try this ways

SET DateFormat='D/M/YYYY';

let varMinDate = num(peek('OrderDate',0,'Facts'));

let varMaxDate = num(peek('OrderDate',-1,'Facts'));

...

...

...

Regards,

Anand

[#8]

That's got it.

Many thanks

[#9]

Thanks - this is now working.