Hi

Been thrown into the deepend on this one. I've got all the cubes the
business needs except one. I've made a virtual cube, combining the Sales
and InventoryValue cubes into one. All Fine

The one I'm having difficulty with Calculation of Inventory Turns as the
Inventory value is not physically stored on a day to day basis, but
calculated "As-Of".

The required formula is Currenty Inventory Value/(CostofGoods Last 3 months)
*4.

I've been playing round in Cube Editor looking at the options, with the
following known "faux pas".

1. The calculation would be a one time, based on current period (month)
2. If using LastPeriods, would have to be (last 4, and subtract current
period ) * 4

Dimensions - Time.
Measures - Inv_Value , COG

I assume this would be best done within the cube and MDX

Here's a rough example of what I hope to achieve. Has anyone come across
this type of quandry before and more importantly, know how to solve it, and
hopefully assist me.

Tia

Squancey

Inv_Val COG COGCalc TurnCalc InvTurn
Jun 0 0
Jul 908 0 0
Aug 854 908 3632
Sep 837 1762 7048
Oct 696 0 2599 10396 14.93678161

I would do this by breaking the formula into 3 parts.

1) Get the Current Inventory Value
2) Get the Cost of Goods for the last 3 months
3) Caculate Currenty Inventory Value/(CostofGoods Last 3 months) *4.

-==================================================

WITH

-- Step 1
MEMBER Measures.CurrentInvValue as 'sum(Tail(FILTER(Descendants
(Time.CurrentMember,,LEAVES),(Time.Currentmember,M easures.Inv_Value) >
0),1),Measures.Inv_Value)'

-- Step 2
MEMBER Measures.COG3Mths as 'IIF(Time.CurrentMember.Level IS
Time.Month,SUM(Time.CurrentMember.Lag
(3):Time.CurrentMember,Measures.COG),NULL)'

-- Step 3
MEMBER Measures.Final as 'Measures.CurrentInvValue / Measures.COG3Mths *
4'

SELECT
{Measures.Inv_Value
,Measures.COG
,Measures.CurrentInvValue
,Measures.COG3Mths
,Measures.Final} ON COLUMNS,
Time.Month.members ON ROWS
FROM <Your Cube Name goes here>

-==================================================

Notes:

I have set up the above MDX query so that it should work in the MDX
Sample app. I have not included all the columns in your sample
resultset, I have just done the calc that you said was giving you the
most trouble.

* Step 1 should always give you the last inv_value for the current time
period at any level.

* Step 2 is filtered so that it only returns a value when you are
looking at the month level (you may want to alter this logic)

HTH

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell