October 21, 2008 at 10:20 am
Hi, I have a time dimension with levels; Year, Quarter, Month and Week. The week belongs to the month in which the Monday of the week falls. Consequently it is possible that in one year a month may have four weeks whereas the year before or after the same month may have five.
When using ParallelPeriod function it seems that when (for example) Time.CurrentMember is Year = 2007, Quarter = 1, Month = 1 and Week = 5 the PP function looks to find the corresponding period from the previous year i.e. Year = 2006, Quarter = 1, Month = 1 and Week = 5. In 2006 week 5 is in Month = 2 so the test fails (there's an Iif statement that tests for the presence of the member in a previous year).
What I'm looking for are ideas on how I can get around this. Should I, for example, use the Cousin function?
Your input will be most valued and appreciated.
Thanks,
Duncan
October 22, 2008 at 5:52 am
I guess the first question is if there is a parallel period. In the case of a week 5 that is in a different month, what should it do? Should you get the data from the week in the different month? If that is the case, are you really going back 1 year, or should you be going back 52 weeks?
This is one of the big problems with the gregorian calendar and the answers are different for each company I have dealt with. The best solution is to change to a fiscal calendar. Fiscal calendars are standardized - every month has the same number of weeks and days, every day number is on the same day of the week, etc. This makes calendar comparisons much more accurate. If you compare a Monday this year to 1 year ago, it is always still a Monday. That can make a big difference if your business has revenue spikes on particular days of the week or during particular weeks in a month.
October 22, 2008 at 6:37 am
Hi, thanks for your input.
Unfortunately I do not have control over the calendar as this is the option chosen by the client.
The function is being used to calculate a cumulative value from the same period (week in this case) plus one from the previous year to the current period (week).
Using fifty-two weeks will not work as about every six or seven years a fifty-three week year comes along.
It's an easy calculation to maintain in T-SQL however there are many of these values to calculate and the overhead in terms of processing time is unacceptable, so we have chosen to move it into the SSAS environment.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply