Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
lauracastagna
Creator II
Creator II

fiscal year and fiscal month

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 DayWeekStart(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

1 Solution

Accepted Solutions
shraddha_g
Partner - Master III
Partner - Master III

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 DayWeekStart(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.



View solution in original post

5 Replies
er_mohit
Master II
Master II

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
Partner - Specialist II
Partner - Specialist II

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

Year(TempDate) As Year
Month(TempDate) As Month
Day(TempDate) As DayWeekStart(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
Partner - Master III
Partner - Master III

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 DayWeekStart(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
Creator
Creator

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;

Thanks & Regards,
Mayil Vahanan R