Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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
In the script file you posted, please read lines 6 & 7 (comment lines).
Best,
Peter
This is from below blog...
It is better to ask there so that hic can answer you.
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?
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
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