Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Maybe like this:
...
dual(text(weekstart(Date)),div(weekstart(Date),14)) as BiWeekDate,
...
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
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!
Maybe like this:
...
dual(text(weekstart(Date)),div(weekstart(Date),14)) as BiWeekDate,
...
Thanks man! That did it!