Creating Bi Weekly in Master Calendar

[#1]

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!

[swuehl]

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

[#3]

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!

[swuehl · Accepted solution]

Maybe like this:

...

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

...

[#5]

Thanks man! That did it!