6 Replies Latest reply: Jan 22, 2013 6:07 PM by Vrockz RSS

    Help with MDX formula

    Vrockz
      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
          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
            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
              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
                John G.
                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
                  John, we donot have blocks concept in ASO cubes, i am trying to write member formula.
                  • 6. Re: Help with MDX formula
                    Vrockz
                    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..