September 17, 2011 at 11:17 am
Hi,
We use 544 accounting year in our company. I have a dimension called Fiscal Period with the Hierarchy below.
Fiscal Period Hierarchy
-----------------------------
Year -> Quarter-> Month -> Week
Example: 2012 -> Quarter 1 -> September -> 1 to 5 will be listed here
My MDX for calculated measures in the cube to find data for previous year using the hierarchy above is not working. It is showing data at the 'All level' but when I drill down to a particular week or year or month, it goes blank. My MDX is as below.
Set [Week]
{existing [Fiscal Period].[Fiscal Period Hierarchy].[Week]}
[LY Quantity Fiscal]
(Sum(existing [Week],(parallelperiod([Fiscal Period].[Fiscal Period
Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember),
[Measures].[Quantity]))
[LY Pounds Fiscal]
(Sum(existing [Week],(parallelperiod([Fiscal Period].[Fiscal Period
Hierarchy].[Year],1,[Fiscal Period].[Fiscal Period Hierarchy].currentmember), [Measures].[Quantity]))
Months in each year are from September to August.
Also, because of 544 fiscal date calculation, my dates will not be the same for each year. For example, Week 1 in September of Fiscal 2011-2012 will have days Aug 29, 2011 to September 4, 2011. If we compare this to last year, Fiscal 2010-2011, days for this Week 1 in September will be from August 30, 2010 to September 5.
I will have 52 or 53 weeks depending on whether the year is a leap year.
Could someone please help me to fix MDX for my measures? Irrespective of the level the user drills down to, I need to show the data correctly comparing it to the same days or weeks in previous year.
September 17, 2011 at 6:10 pm
I fixed this issue by recreating my dimension to drill down to Day level and then creating Sets for calculated measure.
It works fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply