7 Replies Latest reply: Aug 13, 2014 1:31 PM by SabrinaG RSS

    @PREVSIBLING error

    991838

      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
          TimG

          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");
          ENDIF
          
          • 2. Re: @PREVSIBLING error
            LRBS

            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?

            • 3. Re: @PREVSIBLING error
              991838

              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;

              FIX("Actual","100-10",Florida,"Emp1",@RELATIVE(Period,0))

                   Sales

                   (     

                   IF("No_Dept" == #MISSING)

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

                   ENDIF )

              ENDFIX

              • 4. Re: @PREVSIBLING error
                991838

                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
                  SabrinaG

                  Change your member formula to this:

                   

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

                   

                  Sabrina

                  • 6. Re: @PREVSIBLING error
                    991838

                    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..

                    CS1.PNG

                    PPT1.PNG

                    -RN

                    • 7. Re: @PREVSIBLING error
                      SabrinaG

                      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:


                      FIX("Actual","100-10","Florida","Emp1",@RELATIVE(Period,0),"Sales")

                        "D_Flag"(

                        IF("No_Dept" == #MISSING)

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

                        ENDIF)

                      ENDFIX

                       

                      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.

                       

                      Thanks,

                       

                      Sabrina