topic Re: Master Calendar with offset in QlikView

Master Calendar with offset

hobanwashburne — Wed, 27 Nov 2013 15:48:29 GMT

The Master Calendar below seems to work fairly well, but I need to add a Fiscal period which is a 3 month offset from the calendar month (Fiscal Period 201301 = Calendar month 201304 (April)) also our Quarters are also offset Q1 = April thru June. How would I modify the script?

let vMinDate = num(makedate(2006,1,1));
let vMaxDate = num(makedate(2013,12,31));
Let vCurDate = Num(Today());
let vToday = $(vCurDate);

TempCal:
LOAD
date($(vMinDate) + RowNo() - 1 ) as TempDate
AutoGenerate
$(vMaxDate)-$(vMinDate) + 1;

MasterCalendar:
LOAD
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)& '-' & year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal
Order By TempDate asc;
DROP Table TempCal;

Re: Master Calendar with offset

Clever_Anjos — Wed, 27 Nov 2013 15:50:51 GMT

Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY') as FiscalMonthYear,

Re: Master Calendar with offset

Gysbert_Wassenaar — Wed, 27 Nov 2013 16:02:32 GMT

Maybe this:

Date(MonthStart(TempDate,-3), 'YYYYMM') as FiscalPeriod,

'Q'&ceil(month(MonthStart(TempDate,-3))/3) as FiscalQuarter

Re: Master Calendar with offset

hobanwashburne — Wed, 27 Nov 2013 16:40:18 GMT

Any ideas on how to offset the quarters?

Re: Master Calendar with offset

Clever_Anjos — Wed, 27 Nov 2013 17:21:54 GMT

Apply Gysbert formula to Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY')

Re: Master Calendar with offset

hobanwashburne — Wed, 27 Nov 2013 18:13:01 GMT

I'm still missing something

Re: Master Calendar with offset

Clever_Anjos — Wed, 27 Nov 2013 18:23:42 GMT

Date(addmonths(MonthStart(TempDate),-3), 'MMM-YYYY') as FiscalMonthYear,

'Q'&ceil(Month(addmonths(MonthStart(TempDate),-3))/3) as FiscalQuarter