Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted
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

Highlighted
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

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;