9 Replies Latest reply on Aug 29, 2013 5:58 AM by Evgeniy.Rasyuk

    Better way to write this calc?

    927ad62c-64b5-4483-b641-7add92792851

      All Dense are in RED.  All others are Sparse.   The 'A' members are Accounts.  This works fine and runs fast, but just want to make sure it's best practice etc.  I tried doing ElseIf's instead of opening up blocks everytime but it stopped working after about the 20th one.  I'm also unsure on which missing block statement is correct.

       

      SET AGGMISSG ON;
      SET UPDATECALC OFF;
      SET CREATENONMISSINGBLK ON;
      SET CREATEBLOCKONEQ ON;


      Fix ({RTP_Scenario} , {RTP_Year} , "Working" , "No_Location" )

         Fix ( "DPT-90000" , "PRJ-90001-DFLT" , "No_Category" )

       

               "A-65000" (
               If ( @ISMBR("EC-OFFICESUPP")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) ) 
               "A-65000" = "A-65000"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";
               Endif )

       

              "A-65100" (
               If ( @ISMBR("EC-SHIPPING")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) )
               "A-65100" = "A-65100"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";
               Endif )

       

      etc...

       

      etc...

        • 1. Re: Better way to write this calc?
          GlennS_3

          Two things you might try

          1. put all of the calcs into a single member block instead of each one having its own

             "A-65000" (

                   If ( @ISMBR("EC-OFFICESUPP")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) ) 

                   "A-65000" = "A-65000"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";

                   Endif

           

                    If ( @ISMBR("EC-SHIPPING")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) )
                   "A-65100" = "A-65100"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";
                   Endif

          etc...

          )

          this way each block is visited once

           

          2. If you want to be mroe bizarre get rid of the if statements (If statements are slow)

            "A-65000" = "A-65000"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code" / @ISMBR("EC-OFFICESUPP") / @ISMBR("Jan":"Feb") /  @ISMBR(&CurrFcstYr);

           

          @ismbr will return a 1 if it is the member and 0 if it is not. anything divided by 0 is #missing

           


          • 2. Re: Better way to write this calc?
            Anthony Dodds

            My only input on this would be that I would potentially have a first IF for the period year and two plus ten. Mainly for ease of maintenance if or some reason you needed to change it. So:

             

            "A-65000" (

                IF(@ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr))

                     If ( @ISMBR("EC-OFFICESUPP") ) 

                     "A-65000" = "A-65000"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";

                     Endif

             

                      If ( @ISMBR("EC-SHIPPING") )
                     "A-65100" = "A-65100"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";
                     Endif

            etc...

            )

             

            Also, Glenn the only problem I see with the bizarre approach, which is fairly cool though, is that normally when you say @ISMBR(Jan:Feb) you are saying you only want to run a calc for that combination, in the example you describe making all the other instances #missing could potentially clear any data for months you don't want to calculate, i.e actual months with data you don't want to change.

             

            Thanks

             

            Anthony

            • 3. Re: Better way to write this calc?
              GlennS_3

              Anthony,

              You are correct, my way would set other intersections to #missing. Based on what we see in the first two items of the calc, I'm not sure an IF statement is necessary. I don't know the dimensionality of the cube, but it appears you could include all of these in the Fix Statement.

              • 4. Re: Better way to write this calc?
                TimG

                Minor point, but according to the docs CREATEBLOCKONEQ is redundant if CREATENONMISSINGBLK is also used.

                • 5. Re: Better way to write this calc?
                  Anthony Dodds

                  Agreed on the FIX statement.

                   


                  • 6. Re: Better way to write this calc?
                    927ad62c-64b5-4483-b641-7add92792851

                    Thanks.  I went with choice #1.  Any idea why ElseIf doesn't work?

                    • 7. Re: Better way to write this calc?
                      GlennS_3

                      you would have to show me how you tried to implement the Elseif

                      • 8. Re: Better way to write this calc?
                        927ad62c-64b5-4483-b641-7add92792851

                        "A-65000" (   /* Opens up block, thus not Account specific.  All Accounts will calculate */

                         

                                 If ( @ISMBR("EC-OFFICESUPP")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) )    

                                 "A-65000" =  "A-65000"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";

                         

                                 ElseIf ( @ISMBR("EC-SHIPPING")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) )

                                 "A-65100" = "A-65100"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";

                         

                                 ElseIf ( @ISMBR("EC-PRINTING")AND @ISMBR("Jan":"Feb") AND @ISMBR("TwoPlusTen") AND @ISMBR(&CurrFcstYr) )   

                                 "A-65200" =  "A-65200"->"Pre Alloc Actual"->"Final"->"Total Departments"->"Total Locations"->"Total Categories"->"Total Expense Code";

                         

                        Endif )

                        • 9. Re: Better way to write this calc?
                          Evgeniy.Rasyuk

                          my little opinion :

                           

                             where are you fixing dimension for  this members ?!

                              ( "Jan":"Feb") ( "EC-OFFICESUPP","EC-SHIPPING")( "TwoPlusTen" )

                           

                           

                          some code  with using calc other block :

                           

                          -------------

                           

                           

                          SET MSG SUMMARY;

                          SET UPDATECALC OFF;

                          SET AGGMISSG ON;

                          SET EMPTYMEMBERSETS ON; /*  it's must be tuning for this statement    FIX( {RTP_Year} and &CurrFcstYr )  */

                          SET FRMLBOTTOMUP ON;

                          SET CACHE ALL;

                          SET CACHE HIGH;

                          SET LOCKBLOCK HIGH;

                          SET CALCPARALLEL 1;

                          SET CALCTASKDIMS 1;

                           

                           

                          /* preparing target slice */

                           

                          FIX ({RTP_Scenario} , &CurrFcstYr , "Working" , "No_Location" )

                             FIX ( "DPT-90000" , "PRJ-90001-DFLT" , "No_Category" , @LIST "Jan":"Feb"  "TwoPlusTen" )

                               "EC-OFFICESUPP" (

                                 "A-65000" = #Missing;

                               )

                              

                              "EC-SHIPPING" (

                                 "A-65100" = #Missing;

                               )

                             ENDFIX

                          ENDFIX 

                           

                           

                          /* calc from source block to target */

                           

                          Fix (    "No_Location" @LIST("Jan":"Feb"))

                             FIX(  "Pre Alloc Actual"    "Total Departments" "Total Locations"  "Total Categories"   "Total Expense Code" )

                               FIX( {RTP_Year} and &CurrFcstYr )

                              

                                 FIX("EC-OFFICESUPP")

                                  "Final" (

                                  {RTP_Scenario}->"DPT-90000"->"PRJ-90001-DFLT"->"A-65000"->"No_Category" = "A-65000"->"EC-OFFICESUPP";

                                 )

                                ENDFIX

                               

                                 FIX("EC-SHIPPING")

                                  "Final" (

                                  {RTP_Scenario}->"DPT-90000"->"PRJ-90001-DFLT"->"A-65100"->"No_Category" = "A-65100"->"EC-SHIPPING";

                                 )

                                ENDFIX

                               

                                ENDFIX

                              ENDFIX

                          ENDFIX