Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
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)));