topic Re: Last Fiscal Year Flag in Master Calendar in QlikView

Last Fiscal Year Flag in Master Calendar

Thu, 02 Oct 2014 15:51:02 GMT

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

Re: Last Fiscal Year Flag in Master Calendar

proctors — Thu, 25 Jan 2018 20:17:36 GMT

Any solution? I need the same.

Re: Last Fiscal Year Flag in Master Calendar

Sun, 28 Jan 2018 21:59:51 GMT

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