6 Replies Latest reply on May 22, 2018 12:08 PM by 3460247

    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.