I have two dimensions: "CRM Date" ( Time Dimension down to the day ) and
"CRM Client Opportunity". If a client opportunity is "active", it gets an
entry of certain values for that day.

I want to determine how long each opportunity has been active at any point
in time.
Ex: Opp 1 started on Nov. 10 and ends on Nov 20. If I select Nov 15, the
opporunity has been active for 5 days. If I select Nov, the results shuold
be 0 since the opporuntiy is not active at the end of november.

I can't seem to isolate the set of data I want to use. I tried cross
joining all the opportunites with the ClosingPeriod, but then I always get a
duration of 1 since there is only one day.

I also thought of using VBA to subtract the first date from the current
date. But I can't seem to isolate the value of the "first date" to plug into
a vba function. It's also getting very slow:
TopCount(
NonEmptyCrossJoin(
{Descendants([CRM Client Opportunity].CurrentMember, [CRM Client
Opportunity].[Opportunity Description],LEAVES )},
{Descendants([CRM Date].AllMembers, [CRM Date].[Day] )}
)
, 1
)
.Item(0).Item(1)


Any ideas???

This Foodmart query might give you some ideas:

with set [EvaluatedPeriods] as ' Head(
[Month].Members,1).Item(0):ClosingPeriod(month,[1997])'
member [Time].[MonthsWithSales] as ' EvaluatedPeriods.Count -
rank ( tail ( filter ( {EvaluatedPeriods} ,[Unit Sales]=0 ) ).item(0) ,
{EvaluatedPeriods} )'
select
{descendants([1997],month),[MonthsWithSales]} on columns,
[product name].members on rows
from Sales
where [store 3]

considering that:

Foodmart months map to your days

Foodmart years map to your months, and [1997] is the period for which you
want to evaluate your oppprtunities

Foodmart products maps to your CRM opportunities

[Unit Sales] =0 maps to your expression that means an opportunity is NOT
active

HTH,
Brian
www.geocities.com/brianaltmann/olap.html


"Twig" wrote: