[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mondrian
Subject:    [Mondrian] PeriodsToDate and Aggregate problem
From:       Diethard Steiner <diethard.steiner () gmail ! com>
Date:       2010-08-29 11:27:21
Message-ID: AANLkTikWY1UYNw+VrypCN1Uy6uK8ttXQwLcs=TisvOzM () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


Hi,

I've come across an example where my calculated member will yield wrong
results. I've been trying in the last few days to find a solution for this,
but so far no luck.

I have two measures:

[Measures].[Marketing Spending (Actual)]: Aggregation: Sum
[Measures].[New Subscriptions (Actual)]: Aggregation: Sum

Then I created following calculated member in the schema:
[Measures].[CPA (Actual)]=[Measures].[Marketing Spending
(Actual)]/[Measures].[New Subscriptions (Actual)]


If I run a query like this I get the correct results:
SELECT
[Measures].[CPA (Actual)] ON 0
from [Global B2C Weekly KPI Cube]
WHERE
[Date.Weekly Calendar].[2010].[1]:[Date.Weekly Calendar].[2010].[32]

I can even add some other dimensions and all looks fine.

But, when I create a calculated Member in the MDX query with PeriodsToDate
and Aggregate, the results looks like the sum of the CPAs by week.

WITH MEMBER [Measures].[x] AS
Aggregate(PeriodsToDate([Date.Weekly Calendar].[2010],[Date.Weekly
Calendar].[2010].[32]),[Measures].[CPA (Actual)])
SELECT
[Measures].[x] ON 0
from [Global B2C Weekly KPI Cube]

So my take is that as PeriodsToDate gives back all the members, the
calculated measure is calculated for each member and then summed up.

In this case the measures should be summed first for the time period and
then the calculation should be performed. I don't want to create extra
members just to get the sums and then another ember to calculated the CPA.
There must be a more efficient way.

Does somebody know how I can solve this?

Thanks,
Diddy

[Attachment #5 (text/html)]

Hi,<div><br></div><div>I&#39;ve come across an example where my calculated member \
will yield wrong results. I&#39;ve been trying in the last few days to find a \
solution for this, but so far no luck.</div><div><br></div><div> I have two \
measures:</div><div><br></div><div>[Measures].[Marketing Spending (Actual)]: \
Aggregation: Sum</div><div>[Measures].[New Subscriptions (Actual)]: Aggregation: \
Sum</div><div><br></div><div>Then I created following calculated member in the \
schema:</div> <div>[Measures].[CPA (Actual)]=[Measures].[Marketing Spending \
(Actual)]/[Measures].[New Subscriptions \
(Actual)]</div><div><br></div><div><br></div><div>If I run a query like this I get \
the correct results:</div><div><div> SELECT</div><div>[Measures].[CPA (Actual)] ON \
0</div><div>from [Global B2C Weekly KPI Cube]</div><div>WHERE</div><div>[Date.Weekly \
Calendar].[2010].[1]:[Date.Weekly \
Calendar].[2010].[32]</div></div><div><br></div><div>I can even add some other \
dimensions and all looks fine.</div> <div><br></div><div>But, when I create a \
calculated Member in the MDX query with PeriodsToDate and Aggregate, the results \
looks like the sum of the CPAs by week.</div><div><br></div><div><div>WITH MEMBER \
[Measures].[x] AS</div> <div>Aggregate(PeriodsToDate([Date.Weekly \
Calendar].[2010],[Date.Weekly Calendar].[2010].[32]),[Measures].[CPA \
(Actual)])</div><div>SELECT</div><div>[Measures].[x] ON 0</div><div>from [Global B2C \
Weekly KPI Cube]</div></div> <div><br></div><div>So my take is that as PeriodsToDate \
gives back all the members, the calculated measure is calculated for each member and \
then summed up. </div><div><br></div><div>In this case the measures should be summed \
first for the time period and then the calculation should be performed. I don&#39;t \
want to create extra members just to get the sums and then another ember to \
calculated the CPA. There must be a more efficient way.</div> \
<div><br></div><div>Does somebody know how I can solve \
this?</div><div><br></div><div>Thanks,</div><div>Diddy</div><div><br></div><meta \
charset="utf-8"><meta charset="utf-8"><div><br></div><div><br></div>



_______________________________________________
Mondrian mailing list
Mondrian@pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


[prev in list] [next in list] [prev in thread] [next in thread]