Prologika Forums

***** Sorry I've just realised that this is in the wrong place********

Hi everyone,

I'm new to MDX and am having a bit of trouble trying to get the results I need and was hoping someone here could help me.

I'm trying to calculate the age in Months/Periods of a batch of products. I currently have the following:

Ship Date                Product                 Qty

FY08 P01                Keyboard                50

Using:

SELECT ([Measures].[Product Count]) on 0,

           ([Date].[Date Shipped].[Period].&[FY08 P01]) on 1

WHERE ([Product].[Product Type].&[Keyboard])

I want to be able to pass in a date period that will calculate the age of the product relative to the date. I've found that using the below approach to create a calculated measure works for an individual product (i.e. shipped on one date) but not for a group of products (shipped on a range of dates that belong to the same period). For convenience's sake I've used "FY08 P11" as today's Period parameter:

MEMBER [Measures].[Product Age] AS
(
Count(Descendants([Date].[Date Shipped].[Period].CurrentMember.MemberValue:[Date].[Date Shipped].[Period].&[FY08 P11]"))
)

So what I actually want is :

Ship Date                Product                 Qty                  Age of batch in Periods(relative to date parameter - Today)

FY08 P01                Keyboard                50                                10

I'm really stumped so any help would be most welcome!

Thanks

Ben

I assume you want to calculate the age relative to the current date? Then you could simply use the VBA date functions, such as:

MEMBER [Measures].[Product Age] AS
(
VBA!DateDiff("d", [Date].[Date Shipped].[Period].CurrentMember.MemberValue, VBA!Date)
)

Or, if you want to specify another date in the Date dimension, such as Received Date, then substitute the third argument with the CurrentMember of that date dim.