Calendar week cut-off

Tue, 22 Apr 2014 13:31:30 GMT

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;

topic Calendar week cut-off in QlikView

Calendar week cut-off