I have been asked to create a Year on Year calculation. However,

I have been given a time dimension that isn't of type time, with a heirarchy
that is seemingly unusable . I do however have member properties available
that should enable a logical selection.

i.e. If i look at the current member's CalcYear and CalcPeriod property the
year previous year's member that I need would have RelYear and RelPeriod
properties matching these values.

I tried hardcoding the Current member's CalcYear and CalcPeriod property to
-1 and 0 respectively using the following expression which did indeed return
the correct previous year member.

(INTERSECT(FILTER([Period].[Level 03].Members,
CLng([Period].CurrentMember.Properties("Rel Year")) = -1) ,
FILTER([Period].[Level 03].Members,
CLng([Period].CurrentMember.Properties("Rel Period"))=
0)).item(0),[DimCalc].&[1])

however replacing the -1 and 0 with the currentmembers CalcYear and
CalcPeriod property doesn't work (i did clng the result) (I assume because
the context of current member is relative to the filter?)

can anyone help?

(I haven't been using MDX for very long)

P.S. there may be cash in it for a successful answer!

Looks like a set alias should save the current member:

FILTER([Period].[Level 03].Members,
[Period].CurrentMember.Properties("Rel Year") =
[CurrentPeriod].Item(0).Properties("Rel Year") And
[Period].CurrentMember.Properties("Rel Period"))=
[CurrentPeriod].Item(0).Properties("Rel Period)))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the reply, I think a set alias would be a good idea, however the
error message says I can't use a set alias within the generate function.

Since I am essentially trying to return only one member, can you think of
another function that I can use instead of the generate function, or another
way of making the alias apply within the filter expression

regards,

Richard


"Deepak Puri" wrote:

Here's a Foodmart sample of a Set alias in Generate():

Generate({[Time].[1997]} as [ParentTime],
Filter(Descendants([ParentTime].Item(0).Item(0)),
([Measures].[Sales Average], [ParentTime].Item(0))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Sorry Deepack,

I probably wasn't clear enough earlier. I need to use this expression within
a calculated member. When you do this, even in the foodmart example you sent
through, you get the error I mentioned previously.

The topic KPI resolution/solve order??? approaches the problem in a slightly
different way, which might explain what I am trying to do more.

thanks for all your efforts

Richard

(I am using SQLSERVER/AS SP4.)


"Deepak Puri" wrote:

Hi Richard,


Since a calculated member returns a value rather than a set, you could
use something like Sum() instead:

'Sum({[Time].CurrentMember} as [ParentTime],
Sum(Filter(Descendants([ParentTime].Item(0).Item(0),,LEAVES),
([Measures].[Sales Average], [ParentTime].Item(0))
select {[Measures].[Select Sales]} on columns,
{[Time].[1997]} on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

That works perfectly thank you. If you want something from Amazon as a thank
you let me know!

cheers,

Rich

"Deepak Puri" wrote: