7 Replies Latest reply on Aug 13, 2014 6:31 PM by SabrinaG

    @PREVSIBLING error


      Hi Experts, I am trying to write a calc script where I need to copy previous months value from No_Dept to D_Flag if No_Dept = #MISSING for that month..I am not including “Jan” in FIX, so all the members Feb:Dec have a prevsibling, still when I am using it I am getting below error message FIX("Actual","100-10",Florida,"FY14","Emp1",Feb:Dec) SET CREATEBLOCKONEQ ON; Sales ( IF(@CURRMBR(Period)->"No_Dept" == #MISSING) D_Flag=@MEMBER(@PREVSIBLING(Period))->"No_Dept"; ENDIF ) ENDFIX Error: 1200370 Error optimizing formula for [Sales] (line 8): attempt to cross a null member in function [@X] Thanks, -RN

        • 1. Re: @PREVSIBLING error

          I think your formula is a unnecessarily complicated.


          First, you don't need to reference @CURRMBR("Period")->"No_Dept"; in that context it's exactly the same as "No_Dept".

          Second, you should use @PRIOR to get the previous level-zero member in the dimension tagged as Time.  If you have quarter roll-ups, then @PREVSIBLING on "Apr", "Jul" and "Oct" will return empty, not "Mar", "Jun", "Sep" - "Apr" doesn't have a previous sibling if it's the first child of "Qtr2".  I would guess (I don't have your outline) that this is what's actually causing the problem, based on the error message.


          So something like...


          IF("No_Dept" == #MISSING)
               "D_Flag" = @PRIOR("No_Dept");
          1 person found this helpful
          • 2. Re: @PREVSIBLING error
            Geeky Nerdman

            Is department a sparse dimension?  TimG is on the right track.  The @Prior command should do the trick as long as you set the member block on a sparse dimension.  If you set it on a dense dimension, then it will not create a block.  If on sparse, it will. 


            Is the member "No_Dept" in a sparse dimension?

            Is the member "D_Flag" in the same dimension?  If not is it in a sparse dimension?  Or, is it a dense dimension?  Is it the accounts dimension?

            1 person found this helpful
            • 3. Re: @PREVSIBLING error

              Hi TimG,

              Thanks for your inputs, my code is looking little clean now but not giving the desired result..
              Dept transfer result.PNG


              D_Flag = No_Dept;




                   IF("No_Dept" == #MISSING)


                   ENDIF )


              • 4. Re: @PREVSIBLING error

                Hi Lily Rose Thanks for your reply.. Is the member "No_Dept" in a sparse dimension?- Yes Is the member "D_Flag" in the same dimension? - Yes Thanks, RN

                • 5. Re: @PREVSIBLING error

                  Change your member formula to this:


                           "D_Flag" = @PRIOR("Sales");



                  • 6. Re: @PREVSIBLING error

                    Hi Sabrina,

                    Thanks, Works perfect!

                    But as per my understanding the below code should also work... It would be very helpful if you could explain a bit about the below code and why I am not getting the same result as expected..




                    • 7. Re: @PREVSIBLING error

                      The way you have written the code, it does not know from which account to get the prior value. If you wrote it this way:



                        IF("No_Dept" == #MISSING)

                          "D_Flag" = @PRIOR("D_Flag");




                      It would work, because you've fixed on "Sales", so it knows the account. Opening the calculation block with "Sales" tells the script what account to put the result in, but not from which account the prior value must be retrieved. Hope that helps. Also, please mark the question as answered.