Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

lauracastagna
Contributor 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
Partner
Partner

Re: fiscal year and fiscal month

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
Honored Contributor II

Re: fiscal year and fiscal month

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.


Partner
Partner

Re: fiscal year and fiscal month

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

Partner
Partner

Re: fiscal year and fiscal month

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

john9inno
Contributor

Re: fiscal year and fiscal month

you may have error again from Month

Highlighted

Re: fiscal year and fiscal month

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;