Discussion board where members can get started with QlikView.
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
I then load in the master calender
Load min(FactDate) as minDate,max(FactDate) as maxDateresident Facts;Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
SET varToday = Today();
Table Temp; TempCalendar:
$(varMinDate), + Iterno()-1 As Num,
Date($(varMinDate) + Iterno() - 1) as TempDate
While $(varMinDate) + Iterno() -1 <= $(varMaxDate);MasterCalendar:
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
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.
date(floor(FactDate)) as Fact Date ,IDFROMQ:\DEVELOPMENT\HP\QVD\MAIN_ED_2014.qvd(qvd);
Best Regards, Bill
View solution in original post
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