4 Replies Latest reply: Mar 13, 2012 4:29 PM by w00master

# Creating Bi Weekly in Master Calendar

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:

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

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

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

Maybe like this:

...

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

...

• ###### Creating Bi Weekly in Master Calendar

Thanks man! That did it!