6 Replies Latest reply on Jan 23, 2013 12:07 AM by Vrockz

# Help with MDX formula

Hi All,

I have MDX formula which is validating but not producing result, can you guide me in right direction to fix the issue.

What i am trying to accomplish is?

View 1 (Raw)                    View 2 (Adjusted)
ACT     FCST     Calc          ACT     FCST     Calc
X     X     X          X     X     X
0     0     0          0     X     0
0     X     X          X     0     0
X     0     X          0     0     0

If ACT or FCST is '0' in RAW it has replace everything with '0' in Adjusted. else copy Actual and Forecast values to adjusted.

"case when is ([Raw],[Actuals]) = '0' or ( [Adjusted],[ Forecast]) = '0' then '0' else ([Actuals] = [Raw],[Actuals]) and [ Forecast] = ([Raw],[Forecast])" this is my formula which is validated but i don't see any result.

Help will be very mush appreciated.

Vic..
• ###### 1. Re: Help with MDX formula
Hi,

I am not sure which member you are trying to write that formula on. It should be on 'Adjusted' member as that is what you want to calculate. I am not sure how your formula got validated - You can try any of these formula or modify your formula on similar lines and it should work.

CASE WHEN ([Raw], [Act])= 0 OR ([Raw], [Fcst]) = 0
THEN
0
ELSE
[Raw]
END

*******************

IIF ( ([Raw], [Act])= 0 OR ([Raw], [Fcst])= 0 , 0 , [Raw])

Regards,
Sunil

Edited by: sunil k on Jan 17, 2013 2:28 PM
• ###### 2. Re: Help with MDX formula
Hi Sunil,

now its copying data all from Raw into Adjusted. But its doing that even when my ACT or FCST is '0'.

This is the one that is not working properly.
• ###### 3. Re: Help with MDX formula
Well it should work and it should copy RAW to ADJUSTED only if ACT and FCST are both non zero

Did you check that the values are perfect zeros - sometimes your display settings in smart view may make fraction values appear as zero.

Regards,
Sunil
• ###### 4. Re: Help with MDX formula
Could this be a block create issue? What if you load a "1" to the cell you are trying to calculate to in 'adjusted' and then re-run your script.
• ###### 5. Re: Help with MDX formula
John, we donot have blocks concept in ASO cubes, i am trying to write member formula.
• ###### 6. Re: Help with MDX formula
Hi Sunil,

Formula is working now, i have replaced '0' with missing.

Thank q for the help. i tried to mark your reply as correct, but its not letting me.

Vic..