# [Mondrian] PeriodsToDate and Aggregate problem

Diethard Steiner diethard.steiner at gmail.com
Sun Aug 29 07:27:21 EDT 2010

```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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100829/129bf012/attachment.html
```