fiscal year and fiscal month

[Anonymous]

Hi,

I receive the following error:-

Field not found - <Year>

MasterCalendar:

when loading the following script?:-

MasterCalendar: 
Load 
TempDate AS %Date, 
Week(TempDate),'yyyyMMDD' As Week,

Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day,  WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth   // Numeric fiscal month
Resident TempCalendar 
Order By TempDate ASC; 
  Drop Table TempCalendar;

Any help appreciated.

Laura

[er_mohit]

Replace Year with Year(TempDate) and month with Month(TempDate) in fyear field.

Year(TempDate) + If(Month(TempDate)>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year

Mod(Month(TempDate)-$(vFM), 12)+1 as fMonth   // Numeric fiscal month

After made this changes reload it again.

[nagarjuna_kotha]

MasterCalendar: 
Load 
TempDate AS %Date, 
Week(TempDate),'yyyyMMDD' As Week,

Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day,  WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth   // Numeric fiscal month
Resident TempCalendar 
Order By TempDate ASC; 
  Drop Table TempCalendar;

Above underlined was ur issue in script, bcoz you are creating those fileds in the same table only it will not work.

If u want those take again resident of it create ur FYear and FMonth

-Nagarjun

[shraddha_g · Accepted solution]

Another way is using preceding load as below:

MasterCalendar: 

Load *,

Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth;  // Numeric fiscal month

Load 
TempDate AS %Date, 
Week(TempDate),'yyyyMMDD' As Week,

Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day,  WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Resident TempCalendar 
Order By TempDate ASC; 
  Drop Table TempCalendar;

I hope this is helpful.

[john9inno]

you may have error again from Month

[MayilVahanan]

Hi

Try like this

MasterCalendar: 

LOad *,
Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth;   // Numeric fiscal month
Load 
TempDate AS %Date, 
Week(TempDate) As Week,
Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day, 

WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay
Resident TempCalendar 
Order By TempDate ASC; 
  Drop Table TempCalendar;