Hi,



I am trying to write some time series functions Michael Acosta had provided
a good approach here (
http://groups.google.com/group/micro...c3e2de24d0dc4). I
decided to use it and created YTD,QTD, MTD, functions, when I tried to
create some growth formula , i got very strange figures. Deeper tests showed
very strange figures.

For example



When I create some test formula like ([Metrics].[Actual],
Time.CurrentMember) + ([Metrics].[Actual], Time.PrevMember)

([Metrics].[Actual], Time.CurrentMember) - ([Metrics].[Actual],
Time.PrevMember)

([Metrics].[Actual], Time.CurrentMember) * ([Metrics].[Actual],
Time.PrevMember)

([Metrics].[Actual], Time.CurrentMember) / ([Metrics].[Actual],
Time.PrevMember)

While Addition and substraction are always correct, multiplication and
division generates different results for diffrent filters eventhough the
base figures are same. My field is defined as numeric(19,2) in database.

results with multiple selection in Criteria field

Current Last add substract multiply Divide
495,612.39 332,296.51 163,315.88 827,908.90 164,690,267,509.76
1.49

results wilth ALL selected in criteria field
Current Last add substract multiply Divide
495,612.39 332,296.51 163,315.88 827,908.90 100,834,181,131.78
2.63



Any Idea?

Thanks in advance

Forgot to mention

I am testing on Analysis Service 2000 SP3
"erdal akbulut" <erdalim21 (AT) yahoo (DOT) com> wrote

OK please try this

add a MetricID column to sales fact tables in Foodmart 2000.mdb
update all records with MetricID = 0
create a metrics table with two columns MetricID and MetricName
add a record 0, Actual
go to analysis Manager, refresh foodmart data source
create a shared diemension from Metrics table that we created above add
MetricID as level, edit dimension and change member keyname to MetricName
column save this dimension as Metric and process it.
edit sales cube and add this newly created metric diemension
create a calculated member under metric diemension and type 1 in mdx area,
it is a constant.

go to excel and create a pivot from sales cube, add a measures to data area
add a dimension to rows add metric to columns and add some dimension to
filter area, uncheck some of levels from your filter dimension, what is the
result in your new calculated constant,

you can also try change your new formula to
([Metrics].[Actual],Time.PrevMember) this should give you the previous
period's figures when you use it with time diemension return to excel and
time dimension to columns in front of Metric, check the data and you will
see it is working. Go to Cube again and change your formula to
([Metrics].[Actual],Time.PrevMember) + ([Metrics].[Actual],Time.PrevMember)
check te results it should be correct.

Now the Funny part. Change the formula to
([Metrics].[Actual],Time.PrevMember) / ([Metrics].[Actual],Time.PrevMember)
I would expect to get 1 as a result, try changing selected members on your
filter dimensions and see what happens.

I should have missed a very basic part any Idea what I am doing wrong,

thanks
erdal




"erdal akbulut" <erdalim21 (AT) yahoo (DOT) com> wrote

um=2#0f6c3e2de24d0dc4). I

Mostly, I answer my own questions maybe I not am able to explain clearly.
"erdal akbulut" <erdalim21 (AT) yahoo (DOT) com> wrote

Mostly, I answer my own questions maybe I not am able to explain clearly. I
have solved this by changing solve order. I do not know the exact reason,
but it helped me to resolve my issue.

erdal

"erdal akbulut" <erdalim21 (AT) yahoo (DOT) com> wrote

Hi Erdal,

Solve order could, indeed, affect the result, if there are calculated
members on other dimensions/hierarchies as well (like a calculated
measure, or multiple member selections in the Excel filter with AS 2000,
which will create a query calculated member on that
dimension/hierarchy). In such cases, you would need to consider the
effect of changing the evaluation order of calculated members.

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxadvanced_6jn7.asp
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***