Issue with Master Calendar

[Kwame]

This has been generated independently of the master calendar and is populating accurately.

Year(Date(MakeDate(year, quarter * 3 - 2), 'DD/MM/YYYY'))

But once I try with the Year field derived from the master calendar, it appears somewhat scattered or sparse

What could be the issue here?

[rwunderlich]

Can you post your expression using the derived field?

-Rob

[Kwame]

Year(Date(MakeDate(year, quarter * 3 - 2), 'DD/MM/YYYY')) as YEAR

and the date field for my calendar;

Date(MakeDate(year, quarter * 3 - 2), 'DD/MM/YYYY') as %Date

/**************** Generate Calender ********************
CALENDER:
Load 
 Date(TempDate) AS %Date, 
 week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter, 
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
 WeekDay(TempDate) as WeekDay,
 'Q' & Ceil(Month(Date(TempDate,'DD/MM/YYYY')) / 3)&'-' & Year(Date(TempDate,'DD/MM/YYYY')) as QuarterYear ;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('%Date', recno()))-1 as mindate,
 max(FieldValue('%Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('%Date');

[rwunderlich]

It appears your CALENDAR table is not correctly linked to your fact table. What is the subset ratio (from Data Model Viewer preview) on both ends of the %Date field?

-Rob

[Dhirendra_Kharwar__13373]

Your Calendar Date and Fact Table Date are not linked properly. Check following points

1. Date format

2. Calendar creation method

3. If you are creating the date by min-max date then check the Min and Max date format.

[Kwame]

[Kwame]

Script

---

/**************** Generate Calender ********************
CALENDER:
Load
Date(TempDate) AS %Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(Month(Date(TempDate,'DD/MM/YYYY')) / 3)&'-' & Year(Date(TempDate,'DD/MM/YYYY')) as QuarterYear ;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('%Date', recno()))-1 as mindate,
max(FieldValue('%Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('%Date');

---

SET DateFormat='DD/MM/YYYY';

Date(MakeDate(year, quarter * 3 - 2), 'DD/MM/YYYY') as %Date

[rwunderlich]

You have to select the %Date field to see the subset ratio.