I can't seem to visualize a solution for this:

Dimension: Period (year, quarter, month)
Dimension: SuiteLease (has a RentStart property)
Measure: SqFt

What I am trying to do is get a sum of the SqFt where the RentStart for that SuiteLease is within the month of the current Period.

Any help would be appreciated. I think I have to somehow get a set of each dimension, but I'm not sure how to filter them after that. Not even sure which command I should be looking at.

Hello Twig,
this works with foodmart 2000.

With Member Customers.Women as '
Sum(Filter(Customers.[Name].members,customers.currentmember.properties("Gende r")="F"))
'
Select
{customers.women}
on 0 from sales
where(
measures.[Store Sales]
,[Time].[1997].[Q1].[1]
)


HTH
Jörg

"Twig" wrote:

Jörg

Thanks for the reply. It is a step in the right direction, but not quite as far as I need to take it. Where you have properties("Gender")="F", I need to replace the "F" with a comparison to another dimension.

I'm trying to return all the SqFt that is considered "new" in a given period. (The rent start month/year equals the period month/year). This is what I have been playing around with:

WITH
SET CurrentLease AS 'UNION ({ [FSSuiteLease].CURRENTMEMBER} , [FSSuiteLease].[Lease Number].MEMBERS)'

MEMBER MEASURES.NewSqFt AS
' SUM(
FILTER(
Descendants( [Period].CurrentMember, [Period].[Month] ) ,
[Period].CurrentMember.PROPERTIES("Period Compare") = CurrentLease.ITEM(0).ITEM(0).PROPERTIES("Rent Start Compare")
),
[Measures].[Budgeted Sqft]
)
'
SELECT
{ [Measures].[NewSqFt] } ON COLUMNS,
{ [Period].Members } ON ROWS
FROM [Foresight Year]

The properties "Period Compare" and "Rent Start Compare" are just yyyymm strings. In the MDX sample app, I just get #ERR, Property "Rent Start Compare" is invalid. I know it exists, so I just can't get the comparison correct.

I may be totally off base with this. Further suggestions and comments are welcome.

Twig


"Joerg" wrote:

This is just a hint and needs a *lot* of work, but it might help.
This is a Foodmart query:

with member measures.[rentthismonth] as 'sum( filter(store.[store name].members, left(store.currentmember.properties("first opened date"),1)= left(time.currentmember.name,1) ),[unit sales] )'
select
{[Measures].[rentthismonth] } on columns,
[1997].[q2].children on rows
from Sales

I've added a "first opened date" member property to the store dimension.
Note that this works with one-digit months, and that it depends on the exact formats of the property and the time member name. You might create names and properties that suit your needs. This only works at the [month] and [store name] levels of the dimensions.
HTH,
--
Brian
www.geocities.com/brianaltmann/olap.html


"Twig" wrote: