I am currently facing 2 problems

1. with calculated cells on my cube. Bascially I have used a Time dimension
broken into year, week and days and a measure which is aggregated as
distinct count

Imagine for 2004, week 52, I have 7 days and the distinct count for all days
are 2, I am not able to see that the total distinct count for week 52 as 14
(7 *2), is there any way to do tat?

2. I also have a caculate measure which rely on the distinct count measure
to calcualte percentages, the returned values for the 7 days in week 52 is
correct, but it's summing up for the total in week 52 which it should be
actaully doing an average.


Can someone help me out with this or at least point out how to go about
doing this? Thanks

Hello all, I just found out that the 2nd problem is due to the first, but
I'm still trying to figure out how to do a aggregated distinct count value
for my cube. Just to make things clearer, here's an example

Distinct Count Measure
Week 1 Total 3 (<- I need this to be 13)
Days
1 2
2 2
3 1
4 1
5 1
6 3
7 3

I enabled drilldown for the cube and verified that the week1 total is really
a distinct count of 3 only... but what I am really trying to is to count
distinctly for days... but aggregate for Weeks and Years.... Is there anyway
to achieve that?

Any form of advise is greatly appreciated.... thanks in advance



"Nestor" <test (AT) test (DOT) com> wrote

You can use Calculated Member or Calculated Cell.

If you use Calculated Member,

Dcount = IIF(Time.CurrentMember.Level.Name = "Day", [Distinct Count
Measure], Sum(Time.CurrentMember.Children, [Dcount]))

Or if you use Calculated Cell,

Calculation Subcube: {[Measures].[Distinct Count Measure]},
Descendants(Time.Year.Members, Week, SELF_AND_BEFORE)
Calculation Value: Sum(Time.CurrentMember.Children, [Distinct Count
Measure])

But this is the case when you consider only time dimension. I'm not sure you
have to consider more dimensions.

Ohjoo Kwon


"Nestor" <test (AT) test (DOT) com> wrote

Thanks a lot of the help Ohjoo, I'm using calculated member and I'm
inputting the MDX statement into the ValuedExpression, basically this is the
MDX i've keyed into the Value Expression

iif

([My Time].CurrentMember.Level.Name = "Day",

[Measures].[Distinct Products],

iif([My Time].CurrentMember.Level.Name = "Year",

sum([My Time].CurrentMember.Children, [Measures].[New Calculated
Measure]), <-- Error here

sum([My Time].CurrentMember.Children, [Measures].[Distinct
Products])

)

)


What I am trying to do is to count distinctly for days only, for weeks it
should aggregate the days distinct count and for years it should aggregate
the weeks sum. The calculated measure is simply called "New Calculated
Measure"

Can this be done?



count(distinct(<measure to count>), exlcudeempty)


"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Next is simpler.

IIF(Time.CurrentMember.Level.Name = "Day",
[Distinct Products],
Sum(Time.CurrentMember.Children, [New Calculated Measure])
)

Ohjoo


"Nestor" <n3570r (AT) yahoo (DOT) com> wrote

thanks a lot Ohjoo, you've been of great assistances...


"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote