calendar not linking to table

[Anonymous]

hello,

I have used the following (attached) script to create my fiscal calendar (below).  What I don't understand is :-

1. what relevance the Facts table holds as it doesn't seem to be linked to anything?

2. In the attachment he has explained to add the date in the Facts table but I don't understand how as the first Load seems to be a system generated number.  Any assistance appreciated.

Set vNumberOfYears = 2 ;

Facts:
Load

Date(Floor(MakeDate(2017)-365.2424*$(vNumberOfYears)*Rand())) as Date
Autogenerate 1000;

//========= Fast method, using symbol tables =============
MinMaxDate:
Load
Num(Min(Fieldvalue('%DateEmpFormAdded',RecNo()))) as MinDate, 
Num(Max(Fieldvalue('%DateEmpFormAdded',RecNo()))) as MaxDate 
Autogenerate FieldValueCount('%DateEmpFormAdded');

Let vMinDate = Peek('MinDate',0,'MinMaxDate');
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

Drop Table MinMaxDate;


// --- The Calendar --------------------------------------
MonthBasedFiscalCalendar:
Load  // --- Year ---------------
Dual(If($(vCal_FM)=1,Year,(Year-1) &'/'& Year),Year)
as Fiscal.Year,
YearStart as Fiscal.YearStart,
// --- Quarter ------------
Dual('Q' & Quarter, Quarter) as Fiscal.Quarter,
QuarterStart as Fiscal.QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart)
as Fiscal.YearQuarter,
// --- Month --------------
Dual(Month(Date),Month) as Fiscal.Month,
MonthStart(Date) as Fiscal.MonthStart,
Date(MonthStart(Date),'YYYY MMM') as Fiscal.YearMonth,
Mod(Month-1,3)+1 as Fiscal.MonthOfQuarter,
// --- Day ----------------
Date - YearStart + 1 as Fiscal.DayOfYear,
Date - QuarterStart + 1 as Fiscal.DayOfQuarter,
Day(Date) as Fiscal.DayOfMonth,
Date;
Load  // -------------------------------- 
Year(YearEnd) as Year,
Ceil(Month/3) as Quarter,
*;
Load  // -------------------------------- 
AddMonths(YearStart(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1)
as YearStart,
AddMonths(YearEnd(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1)
as YearEnd,
AddMonths(QuarterStart(AddMonths(Date,1-$(vCal_FM))),$(vCal_FM)-1)
as QuarterStart,
Mod(Month(Date)-$(vCal_FM), 12)+1 as Month,
Date;
Load  // -------------------------------- 
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;

[Peter_Cammaert]

In the script file you posted, please read lines 6 & 7 (comment lines).

Best,

Peter

[MK_QSL]

This is from below blog...

Recipe for a 4-4-5 Calendar

It is better to ask there so that hic can answer you.

[Anonymous]

thank-you Peter.  so I shouldn't use it .  but what about :-

"Use Floor(Date) to define the Date key !"

where do I add this in what section?

[Peter_Cammaert · Accepted solution]

Indeed, everything in this Calendar script is usable except the Facts table LOAD. Replace it with your own and make sure your facts table has a Date field. You probably want to write your own Facts table load and then attach the remainder of the Calendar script to make it work like a charm.

Floor(Date) eliminates the time part of the Date field, thereby resetting the value to DD/MM/YYYY 00:00:00.000. Since you are creating a Calendar with just date values, you do not want the time part or your calendar key won't link to your facts table. The Floor() call is probably most needed because rnd() returns a fractional value.

Best,

Peter

[hic]

The Floor function may not always really be needed, but I have much too often seen apps where the "Date" field in the Facts table really is a timestamp with a non-integer value, which leads to problems. So I think it is good practice to always use the floor function for the date, just in case...

HIC