OBIEE Calculate YearWeek and YearWeekAgo

Dates in a datamodel are not always captured in a DateTime dimension. If you need to convert a date to YearWeek (YYYYWW) or to a previous YearWeek, consider this one

cast(year(timestampadd(SQL_TSI_WEEK,0,measuredate)) as
char(4)) right('0' trim( cast(week(timestampadd(SQL_TSI_WEEK,0,measuredate)) As char(2))),2)

If you change the 0 to -1 you get a YearWeekAgo (or 2,3,4)

other intervals are:

• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

Till Next Time