Good morning all, I'm having some issues determining the week of year using IBI...

I'm trying to build out our date dimension. My source is a MySQL table that I created using using an Excel spreadsheet and building out attributes (such as Month Name, Day of Week, etc.) for dates from 1994-2030. One of the attributes that I have is Week of Year. This is calculated using the functionality in excel, and is based on a Sunday - Saturday week. Its my understand, based on what I've seen on the forum, and in my results, that IBI uses a different definition of week than that of Excel.

Ultimately, I'm trying to populate some Y/N flags, such as Week To Date, Previous Week, Prior Year WTD, and Prior Year Previous Week...the problem is that I can't compare the current date's week (that I'm generating in my SQL select) against each row's week number, because they are calc'd differently.

Has anyone done something like this before, and if so how? I read on the forum that people have made some hacks to "WEEKFIRST", but when I look at the text view of my code, I can't find where its setting Weekfirst. I'm new to DMC, so maybe I'm missing something here...

Thanks,
Kent

Hi kent,

You can set the WEEKFIRST in a stored procedure in DMC.

It's not really IBI's interpretation of when a week starts, but more the region where you live in or whatever company standard you have.

Here, in the Netherlands, I always use SET WEEKFIRST = ISO2.

Kent,

Could you explain what Y/N means for Week To Date, Previous Week, Prior Year WTD, and Prior Year Previous Week?
thnx
Endre

Its a flag that the end user will be able to set and use as a limit. For example, they'll be joining this date dimension to a file that contains sales by date for example. If they want to see WTD sales, they can just set a limit on the WTD column of 'Y'. If they want to see sales for the previous week, they would limit on that column as 'Y'. Does that make sense?

Thanks for the suggestion regarding the stored procedure Frans, but unfortunately I wasn't able to get that to return a week that matched the MS excel weeks.

I'm able to get pretty close to what I'm looking to due using Edward Wolfgram's coding suggestion from this topic, but I'm still not sure its going to work 100% of the time.