Problem with aggregation...

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? ThanksHello 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@.test.com> wrote in message
news:Oow6ZqKLFHA.3832@.TK2MSFTNGP12.phx.gbl...
> 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
>|||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@.test.com> wrote in message
news:u$nyDYVLFHA.3988@.tk2msftngp13.phx.gbl...
> 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@.test.com> wrote in message
> news:Oow6ZqKLFHA.3832@.TK2MSFTNGP12.phx.gbl...
> dimension
> days
> 14
measure[vbcol=seagreen]
is[vbcol=seagreen]
>|||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@.olap.co.kr> wrote in message
news:OWnHDMWLFHA.2796@.tk2msftngp13.phx.gbl...
> 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@.test.com> wrote in message
> news:u$nyDYVLFHA.3988@.tk2msftngp13.phx.gbl...
> really
> anyway
> measure
> is
>|||Next is simpler.
IIF(Time.CurrentMember.Level.Name = "Day",
[Distinct Products],
Sum(Time.CurrentMember.Children, [New Calculated Measure])
)
Ohjoo
"Nestor" <n3570r@.yahoo.com> wrote in message
news:OT2O0gbLFHA.1156@.TK2MSFTNGP09.phx.gbl...
> 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[/vbc
ol]
Calculated[vbcol=seagreen]
> 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@.olap.co.kr> wrote in message
> news:OWnHDMWLFHA.2796@.tk2msftngp13.phx.gbl...
but[vbcol=seagreen]
count[vbcol=seagreen]
all[vbcol=seagreen]
52[vbcol=seagreen]
about[vbcol=seagreen]
>|||thanks a lot Ohjoo, you've been of great assistances...
"Ohjoo Kwon" <ojkwon@.olap.co.kr> wrote in message
news:%23jpmrEcLFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Next is simpler.
> IIF(Time.CurrentMember.Level.Name = "Day",
> [Distinct Products],
> Sum(Time.CurrentMember.Children, [New Calculated Measure])
> )
> Ohjoo
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:OT2O0gbLFHA.1156@.TK2MSFTNGP09.phx.gbl...
> the
> Calculated
> but
> count
> all
> 52
> about
>