February 10, 2010 at 10:40 am
Hello,
I had no luck finding an answer in the forums and books for that problem dealing with parallelperiod function in SSAS 2005 :
I have a year-week-date hiearchy on a sales fact table with values like that ones :
Hierarchy in dimension [times]
- level [year]
- level [week]
- level [date]
2009
- Week 1
- Week 2
- Week 4 (yes there is a hole because no sales data for Week 3)
- Week 5
2010
- Week 1
- week 2
- week 3
- week 4
- week 5
to get comparison related to last year's week by week I created a cell calculation using the parallelperiod function like PARALLELPERIOD( [Time].[year-week-date].[year],1) (the third parameter is not supplied so it takes the currentmember by default, which is the the week member)
The problem of this function is that it gets the previous year's week based on the index of the week in the hiearchy. Since this hierarchy is not balanced, for week 3 in 2010 (index # 3 in 2010), it will return the member week 4 in 2009 (because it gets the index #3 in 2009) instead of returning the week 4.
Do you know how to get around this problem of unbalanced hierarchy ?
Thanks a lot
Laurent
Laurent
February 15, 2010 at 5:10 am
A few options suggest themselves. Using LAG instead of PARALLELPERIOD might be a possibility, or you could look at this solution, and adapt to suit your needs.
February 16, 2010 at 7:45 am
Wouldn't the fifty-cent solution be to add a zero sale for week 3?
In fact, you could make a table of zero weeks, join on the week number and sum the sales, that would prevent it happening for some other year.
March 2, 2010 at 5:31 am
Thansk to all. With the excellent litterature that you pointed out I was able to solve my problem.
Laurent
Laurent
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply