Hello

I am trying to create a calculated member that would sum backward in
time given two dates.

The fact table looks like this:

memberid startdateid enddateid geography
1001 20030614 20040724 officeA
1001 20040907 officeA
1002 20040907 officeB
1003 20040907 20040910 officeC
1004 20041001 officeC
1005 20040614 20040724 officeD

I have a measure memb_cnt (default aggregation is distinct count of
memberid). I have created two time ([start date] and [end
date])dimensions and a geography dimension.

I would like to sum backward in time to the earliest start date such
that
[Start Date].CurrentMember<="Selected Start Date" and ([End
Date].CurrentMember>="Selected Start Date" or [End Date].CurrentMember
is null).

This is similar to the George Spofford example on pg 93 but slightly
different - unlike George's example I would like to filter by dates
not sum till a certain total:

1. How do I automatically select the earliest start date from the
[Start Date] Dimension?

2. Is the "Selected Start Date" the same as [Start
Date].CurrentMember?

Any help would be greatly appreciated.

kwaku_duro (AT) hotmail (DOT) com (psmith28) wrote in message news:<4691d105.0410131402.449582b (AT) posting (DOT) google.com>...

sum({[start date].currentmember:[start
date].[year].&[2003].&[6].&[20030614]}, [measures].[memb_cnt])

I have hardcoded the earliest date.

I need help with using mdx to assign the earliest date and to filter
the set used in the sum expression.

kwaku_duro (AT) hotmail (DOT) com (psmith28) wrote in message news:<4691d105.0410160814.3357f6a4 (AT) posting (DOT) google.com>...
I need help with sum backward in time. Please help.

I really need help with this ....


kwaku_duro (AT) hotmail (DOT) com (psmith28) wrote in message
news:<4691d105.0410160814.3357f6a4 (AT) posting (DOT) google.com>...

There are a couple of points to clarify here:

1) Since there can't be a "null" member of the [end date] dimension, do
you mean that the record hasn't yet ended? One way of handling this is
to assign a special date that is larger than any end dates encountered
in the data.

2) For a [start date] to be considered, should all [end date] values for
its fact records fulfill the condition, or at least one?


Assuming that just one record need meet the condition:

'{[start date].[year].&[2003].&[6].&[20030614]}'
Member [Measures].[EndsAfter] as
'iif(Rank(Tail(NonEmptyCrossJoin([end date].[day].Members,
{[start date].CurrentMember}, 1)).Item(0),
[end date].[day].Members) >= Rank(LinkMember(
[SelectedDate].Item(0), [end date]),
[end date].[day].Members), 1, 0)'
Set [EarliestStarts] as
'Filter(NonEmptyCrossJoin(OpeningPeriod([start date].[day])
[SelectedDate].Item(0))), [Measures].[EndsAfter] = 1)'

Select [EarliestStarts] on columns,
{[Measures].[memb_cnt]} on rows
From [MyCube]

- Deepak

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

Deepak

Thanks so much for your response!! The reason end_date is null is
because the record has not ended. In essence, members with null
end_dates are still active. So I am trying to find the total number of
active members at any selected date.

I am still trying to understand your mdx. I will implement the enddate
to be bigger than any date encountered so there are no null end_dates.

You see all I want to able to do is to replicate the following sql in
the cube:

select count(*) from member_table where start_date<='2004-10-01' and
(end_date>='2004-10-01' or end_date is null)

here 2004-10-01 is the selected date and since some members can have a
change of heart and cancel their member as soon as they get home and
talk to their spouses one needs to chk the selected date against
end_date.

Thanks a lot again.

psmith


Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

THe MDX I had suggested was loosely based on the example from p.93 of
"MDX Solutions". But from your description of the problem, a different
approach may be more appropriate. There are a couple of additional
clarifications:

- Can a member have multiple active records, ie. > 1 record whose start
and end dates meet the filter criteria?

- If so, is there a "Member" dimension, down to MemberID?

The problem is that, if a member can have multiple (overlapping) active
records, simply adding the MemberID distinct counts may not always give
the correct reult.


- Deepak

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

Deepak

1. A member can not have multiple active records.

psmith


Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

It should be possible to sum the [memb_cnt] in that case:

With Set [SelectedDate] as
'{[start date].[year].&[2004].&[10].&[20041001]}'
Member [Measures].[ActiveMembers] as
'Sum(Filter(NonEmptyCrossJoin([end date].[day].Members,
{[start date].CurrentMember}, 1),
Rank([end date].CurrentMember, [end date].[day].Members)
Member [Measures].[TotalActive] as
'Sum(NonEmptyCrossJoin(OpeningPeriod([start date].[day])
[SelectedDate].Item(0))), [Measures].[ActiveMembers])'

Select
{[Measures].[TotalActive]} on columns
From [MyCube]

If you need to break out the total by [start date], then the
[TotalActive] measure can be replaced by a StartSet.
(I'm using that both [start date] and [end date] dimensions have similar
structure, so that LinkMember() will work).


- Deepak

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

Deepak

It works!! How did you do this?

I can't make out all the logic of the mdx, but it sures works. If it
is not too much to ask can you annotate the code so I at least get a
sense of what your reasoning is. It will help me to understand the
code and mdx much much better.

Many thanks.

psmith

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote