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.
Thanks in Advance
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
IIF ( ([Raw], [Act])= 0 OR ([Raw], [Fcst])= 0 , 0 , [Raw])
Edited by: sunil k on Jan 17, 2013 2:28 PM
Thanks for your reply, I forgot to mention. I was writing this formula on Adjusted member.
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.
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.