Hello
I have prob with calculating Measures with a timeperiod.

Its about customers in a supermarket.

ROWDATA
CustomerID
VisitStartTime
VisitEndTime
Spending

TimeDimension
Year, Month, Day, Hour; Minute

I know how to calculate Total-Spendings along TimeDimension based on
VisitEndTime, no Prob.

Question:
How can I calculate the number of Customers that are in the Supermarket
(started shopping but have not finished) along TimeDimension??

Thx
Greg

With a [Visits] cube, suppose there are 2 identically structured Time
dimensions like [StartTime] and [EndTime], also a [Customer] dimension.
Suppose [EndTime] is used as the reference Time dimension. Then the
number of customers in the Supermarket at the end of the selected
[EndTime] could be like:

'Sum(Tail(Descendants([EndTime].CurrentMember, [EndTime].[Minutes])) as
[VisitTime],
Count(Filter(NonEmptyCrossJoin(
[StartTime].[Minutes].Members,
[EndTime].[Minutes].Members,
[Customer].[CustomerID].Members),
Rank([StartTime].CurrentMember, [StartTime].[Minutes].Members) <
Rank(LinkMember([VisitTime].Item(0), [StartTime]),
[StartTime].[Minutes].Members) And
Rank([EndTime].CurrentMember, [EndTime].[Minutes].Members) >
Rank([VisitTime].Item(0), [EndTime].[Minutes].Members))))'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!