Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

helen_pip
Contributor II

Master Calender - Does it require aggregated data?

Hello

Do I need to have an aggregated dataset in order for the master calender to work accuratley?

I have a dataset which has more than once row per date

I.e  01/01/2014 could return around 700 rows of data

I have tried applying a master calender to my dataset, and although there is a link on FactDate between my 2 tables, I return 0 results when I start clicking on a list box I.e. Month, MonthYear etc

The script is as follows:

I firstly load in my dataset (More than one row per date) and date is formatted to DD/MM/YYYY

 

Facts:

LOAD FactDate,
ID
FROM
Q:\DEVELOPMENT\HP\QVD\MAIN_ED_2014.qvd
(
qvd);

I then load in the master calender

 

Temp:

Load
min(FactDate) as minDate,
max(FactDate) as maxDate
resident Facts;

Let varMinDate
= Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

SET varToday = Today();

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

FactDate,

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,

InYearToDate(TempDate, $(varToday), 0) * -1 as CurYTDFlag1,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

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

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order

By

TempDate

ASC;

Drop

Table TempCalendar;

Many Thanks

Helen

1 Solution

Accepted Solutions

Re: Master Calender - Does it require aggregated data?

Helen

No, you do not need to have an aggregated dataset in order for the master calendar to work accurately?

Are all your dates in proper date format ?

You could trying forcing this by floor 'ing and date 'ing your FactDate like this.

Facts:

LOAD

date(floor(FactDate))     as Fact Date ,
ID
FROM
Q:\DEVELOPMENT\HP\QVD\MAIN_ED_2014.qvd
(
qvd);

Best Regards,     Bill

View solution in original post

2 Replies

Re: Master Calender - Does it require aggregated data?

Helen

No, you do not need to have an aggregated dataset in order for the master calendar to work accurately?

Are all your dates in proper date format ?

You could trying forcing this by floor 'ing and date 'ing your FactDate like this.

Facts:

LOAD

date(floor(FactDate))     as Fact Date ,
ID
FROM
Q:\DEVELOPMENT\HP\QVD\MAIN_ED_2014.qvd
(
qvd);

Best Regards,     Bill

View solution in original post

helen_pip
Contributor II

Re: Master Calender - Does it require aggregated data?

Thanks Bill

All afternoon I have been trying to solve this problem.....Thank you, this works a treat

Over one hurdle now, now I shall move onto another

Thanks very much

Helen