Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS Calendar Week commencing with split at month Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 4:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:30 AM
Points: 14, Visits: 222

Hi all,

Wonder if someone can help.

Our CFO wants to see a cube calendar that shows week commencing but stops at the end of month.

For example if the dates are

Monday 28th
Tuesday 29th
Wednesday 30th
Thursday 1st
Friday 2nd
Saturday 3rd
Sunday 4th

So for this example the week commencing is the 28th of Month 1 and Thursday is week commencing 1st of the next.

I am having a problem seeing how I can do this.

If I add the weeks to a standard calendar I get the week commencing 28th which includes the 7 days, naturally.

I cannot help feeling I am missing something obvious.

Cheers
Post #978609
Posted Wednesday, September 1, 2010 7:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,819, Visits: 2,563
I think this might give some odd results when browsing the cube. However, it should be do-able.

I would probably keep a natural calendar week as part of the dimension, where weeks always start on Monday(?) and end on Sunday(?).

If I understand your statement you want to end up with partial weeks, not start the weeks on a different day of the week when the months transition. I would probably do something like a week of month column and a start date for week of month column. The week of month would be populated with 1, 2, 3, 4, 5. The start date for the week of the month column would be populated with something like 1-Sep-10, 6-Sep-10, 13-Sep-10, 20-Sep-10 and 27-Sep-10. I would probably also include, depending on your needs, a column that indicates the number of days in that month week. So the first week of September would have 5 as the number of days in the month week #1.

Hope this helps.
Post #978741
Posted Wednesday, September 1, 2010 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:30 AM
Points: 14, Visits: 222
Thanks for that.

I see where you are going. This is a new dimension table for the calendar you specifiy.

Or I could add columns to our existing calendar dimension.

Cheers.
Post #978753
Posted Wednesday, September 1, 2010 9:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,819, Visits: 2,563
I would probably add to the existing dimension.
Post #978879
Posted Wednesday, September 1, 2010 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:30 AM
Points: 14, Visits: 222
Thanks all.

Adding to the existing one is what I am testing now.
Post #978896
Posted Wednesday, May 8, 2013 1:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:00 AM
Points: 1, Visits: 31
I am very new to BI and also having the same requirement of partial weeks between months.
My query is how you calculate Pervious Year MTD and Previous Year YTD with such Date dimensions?

Thanks in advance!
Post #1450438
Posted Thursday, May 9, 2013 8:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:05 AM
Points: 547, Visits: 1,661
So the weeks can have anywhere from 1 to 7 days in them?

How is that useful?

"oh look our revenue this week is 7 times last week, good job team!".
Post #1451166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse