Hi all
I'm looking for some help on an iterative cell calculation. I read somewhere that this can be done using a combination of the calculation pass value and calculation pass depth. I'm still having trouble though.
The easiest way of explaining this is to use an example. Sales reps get incentives based on profit, however incentives affect expenses which in turn affects profits. Using a starting income statement with no incentive about I need to go through about 20 iterations to come an answer.
So basically we have the following lines - (Abridged version )
Income - 10000
Other Expenses - 5000
Incentives = ( Profit * 0.025 )
Profit = ( Income - Other Expenses - Incentives )
Over 20 passes we should have the following
Income - 10000
Other Expenses - 5000
Incentives = 121.95
Profit = 4878.05
Any help would be appreciated.
Thanks
Garron
Hi Garron,
Since your sample only involves iteration, without using data from any specific OLAP cube, the MDX below uses the Foodmart Sales cube only as a place-holder. The CALCULATION_PASS_DEPTH=20 specifies the number of iterations, which should culminate at CALCULATION_PASS_NUMBER = 21. In the Sample MDX App, the Ivalues returned are: Incentives = 121.951, Profit = 4878.049
>>
With Member [Measures].[Income] as '10000'
Member [Measures].[OtherExpenses] as '5000'
Member [Measures].[Incentives] as '0'
Member [Measures].[Profit] as
'[Measures].[Income] - [OtherExpenses]
- [Measures].[Incentives]'
Cell Calculation [IterativeIncentives]
for '({ [Measures].[Incentives] })'
as 'CalculationPassValue([Measures].[Profit],
-1, RELATIVE) * 0.025',
CALCULATION_PASS_NUMBER = 21,
CALCULATION_PASS_DEPTH = 20
select {[Measures].[Income],
[Measures].[OtherExpenses],
[Measures].[Incentives],
[Measures].[Profit] } on columns
from Sales
>>
Here's a link to the AS 2000 BOL section which explains Pass Order:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp
>>
Understanding Pass Order and Solve Order
Two of the most powerful and, correspondingly, most difficult concepts in Microsoft? SQL Server? 2000 Analysis Services, solve order and pass order together determine the manner in which a cube is resolved when queries are processed. This topic assumes that you have a basic understanding of cubes, custom members, calculated members, and custom rollups.
>>
|||Thanks for the help.
Is it possible to do this as a calculated cell within a cube?
Thanks
Garron Mosley
|||Sure, you could create the same calculated members and cells in a cube, I just used query-scoped calculations as an illustration...
sql
