How to calculate week of the month?

Question

Post reply

How to calculate week of the month?

laker_42

SSCarpal Tunnel

Points: 4785
More actions
March 25, 2004 at 1:00 pm

#162230

I am setting up a Time dimension for my olap cube. I would like to be able to set up a level within my time dimension that is the week of the month. So the user would drill down from year to month to week of month to day. Has anyone already tackled this? I would appreciate any help that someone has to offer.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jamison Roberts SSC Enthusiast Points: 194 More actions · Answer 1

Never used cubes, but in Sql there is the datepart() function. You can get the week for anydate like so:

select datepart(wk, '3/25/2004')

>> 13

Apologies if this isn't in the realm of what you're looking for

Antares686 SSC Guru Points: 125444 More actions · Answer 2

I assumed you meant 1st day being the start of week one and each week being a new week. If not you may be able to go from here.

CREATE FUNCTION dbo.WeekInMonth (@date datetime)

RETURNS tinyint

AS

BEGIN

DECLARE @out tinyint

SET @out = (DATEPART(wk,@date) - DATEPART(wk,DATEADD(m,DATEDIFF(m,0,@date),0))) + 1

RETURN ( @out )

END

stevevincent SSC Veteran Points: 265 More actions · Answer 3

Using the SQL techniques described by the other guys, you need to create a table containing a day_id,week_id,month_id and year_id (maybe even a Quarter_ID?), so that every day has a corresponding entry. eg.

Day_ID: 12-03-2004,

Week_ID:10-2004

Month_ID:03-2004

Year_ID:2004

Then the same data for all 7 days in this week, all using Week_ID=10.

And so on...

Store these as pure numbers without the "-" though. It would pay to include some friendly description columns against each number (eg: month description: March 2004, rather than 03-2004)

Use this "Time" table to create a new dimension in Analysis Manager, using each column to define a level in the hierarchy. So, the ID is the Member Key column in the dimension, and the description is the name of the Level. Bear in mind that the Week_ID will not be unique, whereas all the other ID's will be.

HTH

Steve

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 4

Thanks for all of the suggestions. I already had used the datepart(week,@date) to get the calendar week but I also wanted the week in month.

Antares686, thanks for the function. It works perfectly! I had tried to do something similar to this but did not get it to work yet.

I already have my time dimension table created but a user wanted to drill down to the week level so I am adding it to my time dimension table.

Thanks again, you guys are awesome!

John