Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

Last Fiscal Year Flag in Master Calendar

Hello,

Does anyone know how to add a flag for all 365 days of last fiscal year? Currently, I only have LastFYTDFlag. Thanks for any help!

LOAD
CALENDAR_DATE,
FY_START_DATE,
FY_END_DATE,
QTR_START_DATE,
QTR_END_DATE,
MOST_RECENT_BUSINESS_DATE,
Year(CALENDAR_DATE) as YEAR,
Month(CALENDAR_DATE) as MONTH,
Day(CALENDAR_DATE) as DAY,
Weekday(CALENDAR_DATE) as WEEKDAY,
Date(monthstart(CALENDAR_DATE), 'MMM-YYYY') as MONTHYEAR,
Week(CALENDAR_DATE)&'-'&Year(CALENDAR_DATE) as WEEKYEAR,
'Q' &
ceil(month(CALENDAR_DATE)/3) as QUARTER,
inyeartodate(CALENDAR_DATE, Today(),0)*-1 as CurYTDFlag,
inyeartodate(CALENDAR_DATE, Today(),-1)*-1 as LastYTDFlag,
inyeartodate(CALENDAR_DATE, Today(),-1,10)*-1 as LastFYTDFlag,
inyeartodate(CALENDAR_DATE, Today(),0,10)*-1 as CurFYTDFlag

Tags (3)
2 Replies
Highlighted
proctors
Contributor

Re: Last Fiscal Year Flag in Master Calendar

Any solution? I need the same.

Not applicable

Re: Last Fiscal Year Flag in Master Calendar

I'm using the following solution (not written by me):

if(Year(addmonths(YourDate,$(vMonthFactor)))=$(varTodayFY)-1,1,0) as LastFiscalYearFlag

where the variables are defined as:

- Fixed (set permamently):

vFirstFM is set as the month number of the first month in your financial year (7 =July, in our case)

- Dynamic (set in script before the above):

LET vMonthFactor = if($(vFirstFM)<7, -1* ($(vFirstFM)-1),13-$(vFirstFM));

LET varTodayFY=Year(addmonths(Today(),$(vMonthFactor)));