Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i am wondering if someone can help with a new field in a calendar.
basically i want to create a field which flags the first Monday of July in each fiscal year.
so current Fiscal year (2018), i want a flag of 0 set against 03/07/2017
Last Fiscal Year (2017) Flag of -1 against 04/07/2016
Previous Year (2016) Flag of -2 against 06/07/2015
etc
can this be achieved easily, i know i can hard code it but i would prefer something automatic so its done as the fiscal calendar gets extended.
any help would be appreciated.
sample attached.
also, the calc needed to work out the vMaxEndDate for previous years can be ignored, i have just realised that i have variables for these that i can re use.
so its just getting help defining the start dates for each year.
so you have helped getting the flags set against each year
0 = current
-1 = last year
-2 = previosu yeat
etc
what i need is to get an if statement written to pick up all dates >= the date that has the flag of 0 <= vMaxEndDate and set this flag as 0.
e.g.
If(Date>=date that has flag of 0 and Date<='$(vMaxEndDate)',0,
If(Date>=date that has flag of -1 and Date<='$(vMaxEndDateLY)',-1,
etc
im unsure how to get the 'date that has flag of 0' part
Make sense, look at it now...
May be this
LET vMaxEndDate = '09/10/2017';
Dates:
LOAD CALENDAR_DDMMYYYY,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_DAY,
CALENDAR_WEEK_NO,
WEEKDAY,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_PERIOD_NO,
FISCAL_WEEK_NO,
AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,
CALENDAR_MONTH_TEXT
FROM DATES.qvd (qvd);
Left Join (Dates)
LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,
FISCAL_YEAR
Resident Dates
Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;
Max:
LOAD Max(WeekNum) as MaxWeekNum
Resident Dates
Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';
LET vMaxWeekNum = Peek('MaxWeekNum');
LET vLoop = Floor($(vMaxWeekNum)/53);
DROP Table Max;
FOR i = 0 to $(vLoop);
TRACE $(vLoop);
LET vCalc = $(vMaxWeekNum) - ($(i)*53);
TRACE $(vCalc);
Table:
LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,
FISCAL_YEAR
Resident Dates
Where WeekNum = $(vCalc)
Group By FISCAL_YEAR;
NEXT i;
Left Join(Dates)
LOAD *
Resident Table;
DROP Table Table;
FinalDates:
LOAD *,
If(Month(CALENDAR_DDMMYYYY) >= 7 and Day(CALENDAR_DDMMYYYY) >= Day(YearStartDateMonday) and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag
Resident Dates;
DROP Table Dates;
Hi Sunny,
thanks for this.
i see a problem though.
when selecting fiscal year 2018, i should have 0 against all dates from 03/07/2017-09/10/2017
but i am not getting a 0 on any of the 01 or 02 of each month. so its only flagging from 03 onwards.
its the same for the rest of the years too.
is this easily fixed?
My bad, try this
LET vMaxEndDate = '09/10/2017';
Dates:
LOAD CALENDAR_DDMMYYYY,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_DAY,
CALENDAR_WEEK_NO,
WEEKDAY,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_PERIOD_NO,
FISCAL_WEEK_NO,
AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,
CALENDAR_MONTH_TEXT
FROM DATES.qvd (qvd);
Left Join (Dates)
LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,
FISCAL_YEAR
Resident Dates
Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;
Max:
LOAD Max(WeekNum) as MaxWeekNum
Resident Dates
Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';
LET vMaxWeekNum = Peek('MaxWeekNum');
LET vLoop = Floor($(vMaxWeekNum)/53);
DROP Table Max;
FOR i = 0 to $(vLoop);
TRACE $(vLoop);
LET vCalc = $(vMaxWeekNum) - ($(i)*53);
TRACE $(vCalc);
Table:
LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,
FISCAL_YEAR
Resident Dates
Where WeekNum = $(vCalc)
Group By FISCAL_YEAR;
NEXT i;
Left Join(Dates)
LOAD *
Resident Table;
DROP Table Table;
FinalDates:
LOAD *,
If(Month(CALENDAR_DDMMYYYY) >= 7 and CALENDAR_DDMMYYYY >= YearStartDateMonday and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag
Resident Dates;
DROP Table Dates;
Thanks so much Sunny. that works!
i wont bother you again (this week )
have a nice xmas all!