Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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