topic Re: Issues with Master Calendar in QlikView

Issues with Master Calendar

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Issues with Master Calendar

Chanty4u — Thu, 10 Mar 2016 12:47:14 GMT

jst chk dis

Creating A Master Calendar

Re: Issues with Master Calendar

sunny_talwar — Thu, 10 Mar 2016 12:52:05 GMT

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

Re: Issues with Master Calendar

Thu, 10 Mar 2016 12:55:56 GMT

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.

Re: Issues with Master Calendar

its_anandrjs — Thu, 10 Mar 2016 13:10:01 GMT

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

Re: Issues with Master Calendar

sunny_talwar — Thu, 10 Mar 2016 13:17:22 GMT

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

Re: Issues with Master Calendar

its_anandrjs — Thu, 10 Mar 2016 13:19:31 GMT

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

Re: Issues with Master Calendar

Thu, 10 Mar 2016 13:47:23 GMT

That's got it.

Many thanks

Re: Issues with Master Calendar

Thu, 10 Mar 2016 13:57:05 GMT

Thanks - this is now working.