topic Re: Issue with Master Calendar in App Development

Issue with Master Calendar

Kwame — Fri, 15 Nov 2024 21:02:54 GMT

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?

Re: Issue with Master Calendar

rwunderlich — Wed, 28 Feb 2024 17:39:44 GMT

Can you post your expression using the derived field?

-Rob

Re: Issue with Master Calendar

Kwame — Wed, 28 Feb 2024 18:04:14 GMT

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');

Re: Issue with Master Calendar

rwunderlich — Wed, 28 Feb 2024 18:20:25 GMT

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

Re: Issue with Master Calendar

Dhirendra_Kharwar__13373 — Wed, 28 Feb 2024 18:26:18 GMT

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.

Re: Issue with Master Calendar

Kwame — Wed, 28 Feb 2024 18:31:44 GMT

Re: Issue with Master Calendar

Kwame — Wed, 28 Feb 2024 18:41:58 GMT

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

Re: Issue with Master Calendar

rwunderlich — Wed, 28 Feb 2024 22:23:17 GMT

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