This discussion is archived
6 Replies Latest reply: Jan 22, 2013 4:07 PM by Vrockz RSS

Help with MDX formula

Vrockz Newbie
Currently Being Moderated
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.

Thanks in Advance

Vic..
  • 1. Re: Help with MDX formula
    sunil k Journeyer
    Currently Being Moderated
    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
    Vrockz Newbie
    Currently Being Moderated
    Hi Sunil,

    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.
  • 3. Re: Help with MDX formula
    sunil k Journeyer
    Currently Being Moderated
    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
    JohnG. Newbie
    Currently Being Moderated
    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
    Vrockz Newbie
    Currently Being Moderated
    John, we donot have blocks concept in ASO cubes, i am trying to write member formula.
  • 6. Re: Help with MDX formula
    Vrockz Newbie
    Currently Being Moderated
    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..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points