April 1, 2008 at 9:45 pm
Hello Friends
Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.
The Scenario is still the same.
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
I am creating a Calculated Member as measure.
Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )
For Example the result set should be very similar , It is basically doing MTD
Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average
suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
I have used the lag function
AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Currentmember, Measure.abc)
where hirerachy is Year->Month-date
The problem i have here is which i am coming close to conclusion
When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.
AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]
)
When i drill down to date level , which would be assumed
AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]
)
its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days
should i be doing a lag on days.
The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be
suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
i am confused , please help
April 1, 2008 at 9:57 pm
Could you possibly have two parts to your statement - use IF statements to determine the current level for that dimension and then direct the execution down the correct statement path?
So (in psuedo code) for 'IF Level == Month' you use exactly what you have now.
For 'IF Level == Date' you could maybe do a Currentmember.Parent and then lag this as per the moht code (ie getting the parent of the current member [a date level member] will return the month level member. You can't use this in place of what you have now, because then at the month level it won't work (it'll roll back up to years).
HTH,
Steve.
April 1, 2008 at 10:04 pm
Steve
i am getting the concept but i am little confused, by any chance could you suggest me a sample code snippet for a calculated member.
please advise
April 2, 2008 at 10:23 pm
i basically managed to get the calculations working using case command , but while being close to the solution, the totals are derived at Month Hirerachy. Inorder for me to get the correct results i need to replicate the same values at date level.
the query which help to get the solution is
CASE WHEN
Sum
(
[Time].[Report Month].CurrentMember.Lag(2) :
[Time].[Report Month].CurrentMember.Lag(1),
[Measures].[Q1 Count]
)
+
Aggregate
(
PeriodsToDate
(
[Time].[Report Time].[Report Month],
[Time].[Report Time].CurrentMember
),
[Measures].[Q1 Count]
) > 0
THEN
(Sum
({
[Time].[Report Month].CurrentMember.Lag(2) :
[Time].[Report Month].CurrentMember.Lag(1)},
{ [Measures].[Q1 Sum]}
)
+
Aggregate
(
PeriodsToDate
({
[Time].[Report Time].[Report Month],
[Time].[Report Time].CurrentMember
}),
{[Measures].[Q1 Sum]}
))
/
(Sum
({
[Time].[Report Month].CurrentMember.Lag(2) :
[Time].[Report Month].CurrentMember.Lag(1)},
{[Measures].[Q1 Count]}
)
+
Aggregate
(
PeriodsToDate
({
[Time].[Report Time].[Report Month],
[Time].[Report Time].CurrentMember
}),
{[Measures].[Q1 Count]}
)
* 100)
ELSE 0 END
But to conclude with i get the value at month level and the date level hierarchy is empty, i am not sure how i can replicate the same value of month level through out the month at date level cell
please refer the post , inorder to view the result at HTML view.
http://forums.microsoft.com/MSDN/ShowPost.aspx?Post ID109042&SiteID=1
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply