Discussion board where members can get started with QlikView.
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
Any solution? I need the same.
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));