Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating a Dynamic YTD calculation in MDX

I have a YTD calculation that I want to make dynamic based on the real current date.

This is the regular YTD formula:

Sum(YTD([Date].[Calendar Hierarchy].CurrentMember),[Measures].[Planned Orders])

This is the YTD formula hard coded with the current date:

Sum(YTD([Date].[Calendar Hierarchy].[Calendar Year].&[2007].&[2007Q1].&[2007-01].&[2007-01-30T00:00:00]),[Measures].[Planned Orders])

The hard coded date works but I need the date to be dynamic. I’m not sure how to get it to be based from the current date.

I’ve been experimenting with using the NOW() function to return the date but I can’t get the syntax correct.

Thank you.

David

Hmmm i think you can find your answer here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

|||

The article is good but it doesn't address my need to make the date part of the calculation relative and based on the current date (down to the day level).

David

|||

Perhaps this thread can help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996269&SiteID=1

Regards

Thomas Ivarsson

|||

Thomas,

Unfortunately, I have budget like future date information in the cube. As a result, I probably need to develop a dynamic way of capturing the current date.

Would this possible scenario work using your suggestion:

Suppose I created a new fact table with one record where the date changed every day. I have a measure column called "Sales " with a value of 1.

I add this fact table to the cube and rewrite your named set:

Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[lSales])>0))

This would make the date always based on the current date. However, I'm not sure if would filter out other records that I need to show.

David

|||

If you have a measure that is updated daily, like actual sales, my solution will work even if you have a budget measure that points to future dates.

This link have some other suggestions.

http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

HTH

Thomas Ivarsson