Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am very much a newbie and I am trying to figure out how to get the yearly cut-off dates correct in my calendar. Here is my pre-defined data-set that I have received help building;
I am using a week-counter for the period in between the below two dates. Basically:
ArticleStartDate = Mon Jan 1st
ArticleEndDate = Mon Jan 14th
= 2 weeks, with count of 1 for calendar week 1 and 2 respectively.
Using this logic we have defined the calendar below to not count any week with an ArticleStartDate on a Wednesday or later as current week. Please find the script relating to this below. My problem is this; Any week-count is correct using this logic up until the cut-off between a Fiscal Year or Fiscal Quarters. Good example is this; Our fiscal year ends 30 Sept. This date occurred on a Monday in 2013. I would like to use the same logic for this cut-off as for the ArticleStartDate and ArticleEndDate, basically the week starting on Monday Sept 30 should be included in Fiscal year 2014/2015 instead of (as it is now) 2013/2014 (i.e. Tuesday Oct 1st would count as StartDate and since this is before Wednesday it should therefore be counted as current week being First week of FY 2014/2015).
If anyone can find the time to help me with this problem I would be forever grateful
Best regards
/Mattias
//*************WEEK COUNTER******************
if(weekday(ArticleStartDate) > 2, Date(Floor(ArticleStartDate)) + 7, Date(Floor(ArticleStartDate))) as StartDate_QV,
if(weekday(ArticleEndDate) < 3, Date(Floor(ArticleEndDate)) - 7, Date(Floor(ArticleEndDate))) as EndDate_QV
NoConcatenate load Distinct
*,
week(StartDate) as Week_Transaction,
weekstart(Date(StartDate_QV + IterNo()-1 )) as ReferenceDate,
weekstart(Date(StartDate_QV + IterNo()-1 )) as %DateID,
num(WeekDay(StartDate_QV)) as Day1,
1 as Counter,
1 as Counter_Week
Resident [Main Data Temp]
while (IterNo() <= (num([EndDate_QV]) - num([StartDate_QV])) + 1 )
//**********CALENDAR************
MinDate:
LOAD
ArticleStartDate as MinDate.ArticleStartDate
Resident [Main Data]
Order by ArticleStartDate asc;
LET vMinDate = NUM(PEEK('MinDate.ArticleStartDate', 0, 'MinDate'));
drop table MinDate;
MaxDate:
LOAD
ArticleEndDate as MinDate.ArticleEndDate
Resident [Main Data]
Order by ArticleEndDate asc;
LET vMaxDate = NUM(PEEK('MinDate.ArticleEndDate', -1, 'MaxDate'));
drop table MaxDate;
SET vLFM = 3; // 3 = three remaining months in calendar year
// Step 2: Create temporary calendar: one row for each tday
Calendar_Temp:
LOAD
num($(vMinDate) + ROWNO() - 1) AS Datefield,
addmonths(num(($(vMinDate) + ROWNO() - 1)), + $(vLFM)) AS Datefield_FY
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;
// Step 3: Create final calendar
Calendar:
LOAD
// Dates
Datefield,
Datefield as %DateID,
Datefield_FY,
// Year
year(Datefield_FY) as Year_FY,
year(Datefield) as Year_CY,
// Week
week(Datefield_FY) as Week_FY,
week(Datefield) as Week_CY,
if(Week(Datefield)>40,Week(Datefield)-40,Week(Datefield)+15) as Week_CY_Sequence,
// yearname(Datefield,0,10) as YearName_FY,
num(month(Datefield_FY),00) as Period_FY,
MONTH(Datefield) AS Month,
day(Datefield) as Day,
WeekDay(Datefield) as WeekDay,
num(Month(Datefield),00) AS MonthNum,
year(Datefield) &'-'& month(Datefield) as YearMonth,
year(Datefield) &'-'& num(month(Datefield),00) as YearMonthNum,
'Q' & ceil(Month(Datefield)/3) AS Quarter_CY,
'Q' & ceil(Month(Datefield_FY)/3) AS Quarter_FY,
Year(Datefield) & '-Q' & ceil(Month(Datefield)/3) AS YearQuarter
RESIDENT Calendar_Temp;
drop tables Calendar_Temp;