fYear not found error whilst creating calendar

[Anonymous]

Hi,

I'm using the following to create the dual and fiscal calendar but I am receiving the following error.  any assistance appreciated.

Error = Field not found - <fYear>

..........................................................................

QuartersMap:
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(%DateEmpFormAdded) as minDate, 
max(%DateEmpFormAdded) as maxDate 
Resident EmployersBoard; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
Set vMonthFactor = 7;
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

Set vMonthFactor = 7;

Calendar: 
Load 
TempDate AS %DateEmpFormAdded,
Dual(fYear-1 &'/'& fYear, fYear)  as FYear,  // Dual fiscal year
Dual(Month, fMonth)  as FMonth,  // Dual fiscal month
Year + If(Month>=$(vMonthFactor), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1  as fMonth,  // Numeric fiscal month
  
Year(TempDate)  as Year,  // Your standard master calendar
Month(TempDate)  as Month

Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[hic]

The behaviour is correct. There is no fYear in available where you reference it. You can only use the fields available in the input table defined by "Resident TempCalendar".

However, if you use preceding Load, you can create a step-wise definition of your fields. See Preceding Load.

HIC

[Anonymous]

Hi Henric,

I did read the link but am still struggling with this.  Are there examples that others have used that you can please direct me to so I can get a real example?

Cheers

Laura

[hic · Accepted solution]

Try

Load 
TempDate AS %DateEmpFormAdded,
Dual(fYear-1 &'/'& fYear, fYear)  as FYear, // Dual fiscal year
Dual(Month, fMonth)  as FMonth, // Dual fiscal month
*;
Load
Year + If(Month>=$(vMonthFactor), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1  as fMonth, // Numeric fiscal month
*;
Load  
Year(TempDate)  as Year, // Your standard master calendar
Month(TempDate)  as Month,
*
Resident TempCalendar  

[hic]

See also Calendars

HIC

[tyagishaila]

In Calendar Table You have to use preceding load to use the field fYear and fMonth

Calendar:

Load *, Dual(fYear-1 &'/'& fYear, fYear)  as FYear,  // Dual fiscal year
Dual(Month, fMonth)  as FMonth,  // Dual fiscal month;

Load *,

Year + If(Month>=$(vMonthFactor), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vMonthFactor), 12)+1  as fMonth,  // Numeric fiscal month;
Load 
TempDate AS %DateEmpFormAdded,
Year(TempDate)  as Year,  // Your standard master calendar
Month(TempDate)  as Month

Resident TempCalendar 

[Anonymous]

Thank-you Henric.

I am using the calendar from the script (from hyperlink you gave me in last reply).  I have chosen to use the fast method for Min/Max.  My calendar is not linking to my table (date field = '%DateEmpFormAdded').  My script so far is below.

Questions please:-

In Step 1. you mention to start by Loading your facts table and to Use Floor(Date) to define the Date key !!" .  My date field is '%DateEmpFormAdded' .  The facts table becomes an island table.  I dont understand where to add the Date field ?

...........................

Concatenate(EmployersBoard)
LOAD
EmpState
,'Lead' as Type
,ItemCreatedWhen as %DateEmpFormAdded

...................Script for Calendar.................

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 Fiscal Calendar --------------------------------------
MonthBasedFiscalCalendar:
Load  // --- Grain = Year ---------------
Dual(If($(vCal_FM)=1,Year,(Year-1) &'/'& Year),Year)
as Fiscal.Year,
YearStart as Fiscal.YearStart,
// --- Grain = Quarter ------------
Dual('Q' & Quarter, Quarter) as Fiscal.Quarter,
QuarterStart as Fiscal.QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart)
as Fiscal.YearQuarter,
// --- Grain = 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,
// --- Grain = 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 ;

Regards,

LC