topic Creating Bi Weekly in Master Calendar in QlikView

Creating Bi Weekly in Master Calendar

Mon, 12 Mar 2012 22:20:39 GMT

Been trying to work this out, but I must be missing something. I'd like to add in bi-weekly increments (e.g. every two weeks instead of weekly). What's the best way of going about this?

My master calendar looks like the following:

FactCalendar:

LOAD

TempDate AS CalendarDate,

Day(TempDate) AS CalendarDay,

Week(TempDate) AS CalendarWeek,

Weekday(TempDate) AS WeekDay,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,

Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

WeekStart(TempDate, 0, -1) AS CalendarWeekStart,

num(TempDate) AS DayNum

Thanks!

Creating Bi Weekly in Master Calendar

swuehl — Mon, 12 Mar 2012 23:58:44 GMT

You can derive your BiWeekly incremented field value from week, maybe like

ceil(week(Date)/2) as BiWeek

but for some years, you will have an inconsistent BiWeekly increment at year's end (years with week number 53).

You can use something like

div(weekstart(Date),14) as BiWeek

to create a continuous Biweekly numbering across all years.

Hope this helps,

Stefan

Creating Bi Weekly in Master Calendar

Tue, 13 Mar 2012 14:40:13 GMT

Stefan-

This is really great. I used the div(weekstart(Date),14) expression. It's something that I can use in my setanalysis! One follow-up is applying real date to these bi-weekly values? I'd like for the user to be able to select biweekly dates from a list box.

Thanks!

Creating Bi Weekly in Master Calendar

swuehl — Tue, 13 Mar 2012 14:48:15 GMT

Maybe like this:

...

dual(text(weekstart(Date)),div(weekstart(Date),14)) as BiWeekDate,

...

Creating Bi Weekly in Master Calendar

Tue, 13 Mar 2012 20:29:36 GMT

Thanks man! That did it!