XRange issue

3460247

    Hi All,

     

    "May" = @Xrange("Jan"-> "Actual"->"Final","Apr"->"Actual"->"Final")-@Xrange("Jan"->"Current_Forecast"->"Current","Apr"->"Current_Forecast"->"Current")+

            "May"->"Current_Forecast"->"Current";

     

    The above code sshowing the Error:

     

     

     

     

     

     

     

     

    Essbase
    Error(1200400): Error executing formula for [May] (line 0): scalar double
    argument expected in function [@SUB]

     

     

    Please help me how to write the code?

      • 1. Re: XRange issue
        ManojPavan-Oracle

        Hi,

         

        I do not believe you can Subtract 2 different Xrange lists. ((a->d, b->d, c->d) - (c->e, c->f,c->g)) will not be a valid operation.

         

        Do you want to return the sum of members mentioned in the following list?

         

        @Xrange("Jan"-> "Actual"->"Final","Apr"->"Actual"->"Final")

         

         

        • 2. Re: XRange issue
          3460247

          Thanks,

           

          our requirement is subtraction of

          Jan"-> "Actual"->"Final","Apr"->"Actual"->"Final")-@Xrange("Jan"->"Current_Forecast"->"Current","Apr"->"Current_Forecast"->"Current")+

                  "May"->"Current_Forecast"->"Current";

           

           

           

           

          So what are the possiablity to write the subtraction  of the range list., Pleasse help me

          • 3. Re: XRange issue
            ManojPavan-Oracle

            Hi,

             

            Do you want to the summation of these four values?

             

            ("Jan"-> "Actual"->"Final" +"Feb"-> "Actual"->"Final"+"Mar"-> "Actual"->"Final"+"Apr"->"Actual"->"Final")

             

            or you want to subtract each member as below,

             

            "Jan"-> "Actual"->"Final" - "Jan"->"Current_Forecast"->"Current"

            "Feb"-> "Actual"->"Final"- "Feb"->"Current_Forecast"->"Current"

            "Mar"-> "Actual"->"Final" -"Mar"->"Current_Forecast"->"Current"

            "Apr"->"Actual"->"Final" - "Apr"->"Current_Forecast"->"Current"

             

             

            -

            Manoj

             

            • 4. Re: XRange issue
              3460247

              Thanks Manoj.

               

               

              How to write the below code, please find the below example

              E= (a+b-c+d)+E

               

              May"->"Current_Forecast"->"Current= @Xrange("Jan"-> "Actual"->"Final","Apr"->"Actual"->"Final")-@Xrange("Jan"->"Current_Forecast"->"Current","Apr"->"Current_Forecast"->"Current")+

                      "May"->"Current_Forecast"->"Current";

                                                                                                              

              • 5. Re: XRange issue
                ManojPavan-Oracle

                If you want have the summation as I mentioned above you can try some thing as below,

                 

                @Xrange("Jan"-> "Actual"->"Final","Apr"->"Actual"->"Final") can be written as,

                 

                @SUMRANGE("Actual",@Xrange("Jan"->"Final","Apr"->"Final"));

                 

                This will give the sum of all the values for xrange member combination. Please test the first part of the code by populating the value to a dummy member and see if you are getting desired results.

                 

                If you are getting the desire results the final formula can be some thing as follows,

                 

                May"->"Current_Forecast"->"Current= (@SUMRANGE("Actual",@Xrange("Jan"->"Final","Apr"->"Final"))) - (@SUMRANGE("Current", @Xrange("Jan"->"Current_Forecast","Apr"->"Current_Forecast")+("May"->"Current_Forecast"->"Current");

                • 6. Re: XRange issue
                  3460247

                  Thanks for Update.

                   

                   

                   

                  FIX (@Relative("UIT9000",0),@Relative("TWW9000",0 ),"No_Type","S30000", "USD","No_Client_Source",&SV_Optima_ActCrYr,{RTP_Optima_Scenario_Target},{RTP_Optima_Version_Target})

                  {RTP_Optima_Period_Target} = @SUMRANGE({RTP_Optima_Scenario_Source1}->{RTP_Optima_Version_Source1},"Jan":{RTP_Optima_Period_Source})-@SUMRANGE({RTP_Optima_Scenario_Source2}->{RTP_Optima_Version_Source2},"Jan":{RTP_Optima_Period_Source})+ {RTP_Optima_Period_Target}->{RTP_Optima_Scenario_Target}->{RTP_Optima_Version_Target};

                  EndFIX

                   

                   

                  AS per My Requirement is below :

                   

                  Column N is “May” month for which true up has been done

                  Source 1 è Actual , Final

                  Source 2 è 2018-Mar_RF and Final

                  Target è Current Forecast and Current

                  Source period YTD è “Apr”

                   

                  For the above conditions when the rule is run ;

                   

                  Column N is the may month calculation that gets overwritten

                  Column O is the May month existing value BEFORE it is overwritten.

                  Column P is the True up calculation done manually

                  Column R is the “Check” and it should show 0 always for the rule to be correct – formula used is  ‘column N – Column P’ – All defects highlighted in RED.

                   

                  As the above requirement , How to write the code ? please help.