4 Replies Latest reply on Jul 10, 2018 8:13 PM by 2673365

    PBCS - Sumrange is not working as expected

    2673365

      Hello everyone,

       

        Trying to write an allocation script, it needs to exclude some members from the entity, those members will be provided at the prompt since Entities/members/exclusion might change based on depts.

       

      I am trying to use total entity and sum the entities provided by user at prompt, then minus from total entity:

       

      Account and Periods are dense, all other dims are sparse.

       

      Here is the script:

      SET CACHE HIGH;
      SET AGGMISSG ON;
      SET CALCPARALLEL 3;

      FIX(Working,&CurrYr,Local,@Relative(Yeartotal,0))
      FIX("Dept_1",@RELATIVE(Net_income,0),@RELATIVE(Product,0),@RELATIVE(Entity,0))

      SET CREATENONMISSINGBLK ON;

      Budget(

      Budget = ((USD->Actual->Final->FY17->Yeartotal/ (USD->Actual->Final->FY17->Yeartotal->Entity -

      @SUMRANGE(USD->Actual->Final->FY17->Yeartotal,@LIST(Ent_1,Ent_2,Ent_3));)

      * No_Product->No_RC->Yeartotal->Budget)/12;

      )
      SET CREATENONMISSINGBLK OFF;
      ENDFIX
      ENDFIX

        • 1. Re: PBCS - Sumrange is not working as expected
          Jonathan A Cohen

          What is the unexpected result?

           

          You have a semicolon within the calculation (after the SUMRANGE) that does not belong.

           

          You do not have any runtime prompts.  Any user prompts have {} brackets.  Eg.  {Entity}

          • 2. Re: PBCS - Sumrange is not working as expected
            2673365

            Hi Jonathan,

             

            In this example, I am using individual accounts, products, and Entity, then I am adding entities that need to be subtracted from total entities

             

            Dept1,Act1,Ent1 = 10

            Dept1,Act1,Ent2 = 10

            Dept1,Act1,Ent3 = 10

            Dept1,Act1,Ent4 = 10

            Dept1,Act1,Ent5 = 10

            Dept1,Act1,Entity = 50

             

            I need to exclude ent1, ent2 and ent3, so I am adding enti1, ent2 and ent3 (30) and then subtracting from Entity(50). Therefore, I am expecting 50% each for Ent4 and Ent5.

             

            All the pieces working except Sumrange, it looks like sumrange only bringing one entity instead of adding both entities.

             

            I had to modify script after pasting it here but below is the parsed syntax.

            I haven't added Prompt since I am still testing with couple of entities to see if it returns proper results.

             

            SET CACHE HIGH;
            SET AGGMISSG ON;
            SET CALCPARALLEL 3;

            FIX(Working1,&NxtYr,Local,@Relative(Yeartotal,0))
            FIX("Dep1",@RELATIVE(netIncome,0),@RELATIVE(Product,0),@RELATIVE(Entity,0))

            SET CREATENONMISSINGBLK ON;

            Budget(

            Budget = ((USD->Actual->Final->FY17->Yeartotal/ (USD->Actual->Final->FY17->Yeartotal->Entity -

            @SUMRANGE(USD->Actual->Final->FY17->Yeartotal,@LIST(Ent1,ent2,ent3))
            )

            * No_Product->No_entity->Yeartotal->Budget))/12;

            )
            SET CREATENONMISSINGBLK OFF;
            ENDFIX
            ENDFIX

            • 3. Re: PBCS - Sumrange is not working as expected
              Jonathan A Cohen

              Let's try a couple of things:

               

              1. Remove the @LIST from SUMRANGE.  Not logical that it would not work, but stranger things have happened.

               

              2. Replace the SUMRANGE with a manual sum of the three values for now.  To make sure you get the expected result.

               

              USD->Actual->Final->FY17->Yeartotal->Ent1 + USD->Actual->Final->FY17->Yeartotal->ent2 + USD->Actual->Final->FY17->Yeartotal->ent3

              • 4. Re: PBCS - Sumrange is not working as expected
                2673365

                  Good point Jonthan, it didn't work for single addition either(without sumrange). It's strange that within the same products it works but across product doesn't work. Also, I put 1 before running the calc, it changes back to #missing. So I know it's touching that cell but replacing it with missing.

                 

                USD->Actual->Final->FY17->Aug->Ent1;

                 

                I tried changing Budget with Begbalance(Dense) to see if Sparse on the left side is the problem but no luck.

                 

                At the end I added product and it's started showing the values, but the issue is that I can't use @currmbr(product) function since entity could be at any product.

                 

                Is there any function that tells Sumrange to do sum across sparse members?