A Distinct Count measure can't be rolled up using Sum, and I don't think
that the Aggregate() function can be applied to it either. AFAIK, you
can't roll up a Distinct Count measure directly on-the-fly. In case the
entities counted by your Distinct Count are also broken out in a
dimension, then aggregation can be done by testing for Empty members.

- Deepak

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

Why do you need to subtract 1 from the count? The logic to change a -1
result to 0 is causing re-calculation of the NonEmptyCrossJoin (unless
the result is cached somehow). Unfortunately, there is no Max() function
in VBA to help.

What is the hierarchy of your [Cases] dimension like - how many levels
and members? In previous threads, there were ways to speed up
NonEmtpyCrossJoin for specific hierachies.

- Deepak

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

Hi Deepak,

I need to subtract a 1 at the end because every value on the Rolling
Time measure has been incremented by 1. I am assuming it is because of
the 'All' member in the Cases dimension.

Furthermore, I need the NECJ formula twice because when I have
subtracted the -1, I was getting values of -1, when it should really be
a 0.

There's only one level (on viewing) in the Cases dimension and about
250000 members. So, (on design), there's two levels but I hide the first
level. I hope this makes sense.

Thanks so much for your time,

Audrey




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

Hi Deepak!!

It works!! Still a little slow but I think it's much better than what it
was before. I suppose with the distinct count function and the NECJ
formula, I should not expect great performance.

I think the NECJ will eventually get more complicated. =( =(
The time dimension has three levels. (i.e. Year, Quarter, Month). For
now, the Rolling Time is works great for the month level only, hence,
the LastPeriods(12,[Time Fiscal].CurrentMember), but I think it might
get complicated for the quarter level and the year level. I'm supposing
there will be a bunch of IFF statements to accomodate those levels.

If you can think of a better way to deal with the month level and year
level. Let me know.

Thanks again,
Audrey





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

Hi Deepak,

Thanks for the solution. I haven't tried it yet but definitely will.
How's the performance on this query? It looks fairly complex.

Thanks again,

Audrey



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

Hi Deepak,

Me again with the Rolling Time problem.
The formula that we previously came up with works well if there's one
distinct count measure.

But now, I have two other measures (not distinct) in the cube. Using the
formula that I have only accounts for the distinct count measure.

This is what I have:

IIF([Measures].CurrentMember = [Measures].[No of Cases],
NonEmptyCrossJoin({[Cases].[Case Key].Members},
{[Locations].CurrentMember},
{[Time Series].[Value]},
{[Measures].CurrentMember},
{[Intent].CurrentMember},
{[Staging].CurrentMember},
{[Calendar Time].CurrentMember},
LastPeriods(4,[Fiscal Time].CurrentMember),1).Count,

Sum(LastPeriods(4,[Fiscal Time].CurrentMember), [Time Series].[Value]))

The three measures are: No of Cases (Distinct COunt)
Total Cycles (Sum)
Starting Cycle (Sum


I hope this makes sense.

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

Hi Audrey,


I assume that [Time Series] is a time-analysis dimension, and that
this rolling time formula will define a member of that dimension. You
may consider changing the '=' comparison to 'is' comparison in the
beginning; but is the MDX working as you expect otherwise?


Audrey Ng <odd26uk (AT) yahoo (DOT) co.uk> wrote

Hi Deepak,

The Rolling Time issue always seem to creep up on us. The following
query which you have provided works well:

NonEmptyCrossJoin({[Cases].[Individual].Members},
{[Locations].CurrentMember},
{[MR Measures].CurrentMember},
{[Programs].CurrentMember},
{[Staging].CurrentMember},
{[Time Calendar].CurrentMember},
LastPeriods(12,[Time Fiscal].CurrentMember),1).Count

But as you notice in the NECJ, there are two Time dimensions. One to
illustrate 'Fiscal Time' and the other one is to illustrate 'Calendar
Time'. The only issue with this is that we are counting the last 12
members of ONLY the [Time Fiscal] dimension. I would like to count BOTH
the [Time Fiscal] and [Time Calendar]. Is that possible?


Hope this makes sense,

Audrey

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

Hi Audrey,

If I understand this correctly, what you want is to select a given
fiscal and a given calendar month, then count the cases in a rolling
12-month period for both these months. In that case, you should be
able to substitute:

LastPeriods(12,[Time Calendar].CurrentMember

in place of: {[Time Calendar].CurrentMember}


- Deepak



Audrey Ng <odd26uk (AT) yahoo (DOT) co.uk> wrote

Guys,

I have a similar kind of cube which has incremental Distinct counts
spreadout in the time dimenions. and My need is UpTodate Distinct count
of ( History before year1 , incremental of year1, incremental of Year2 )

like
distinct count of (

PeriodstoDate([Time].[(All)],[Time].CurrentMember),
[Measures].[ProductCount]

)

Could any one tell me how to go about. it?

Thanks,
SAGI




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