3 Replies Latest reply: Aug 10, 2018 3:24 AM by MATEUSZ TUCHOLSKI RSS

    aggregation help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below issue.

      I have calculated CC1% and CC2% with below formulas.

      Here Each Name contains multiple SCOPE values. by default CC1%   and CC2% are showing wrong values but when I have filtered one particular Name =aim1 getting correct percentage values.

      Please find the below attached image. Please help me to get the related percentage values of aim1 and aim2 without filtering.

       

      Please find the attached sample app and sample excel with calculations in the second sheet.

       

      results.png

       

      CC1% :

      Sum(TOTAL <SCOPE>

      Aggr(

        RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

      , Date, SCOPE, Name )

      )

      /

      Sum(TOTAL

      Aggr(

        RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

      , Date, SCOPE, Name )

      )

       

      CC2%:

      Sum(TOTAL <SCOPE>

      Aggr(

        RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

      , Date, SCOPE, Name )

      )

      /

      Sum(TOTAL

      Aggr(

        RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

      , Date, SCOPE, Name )

      )

       

       

      Thanks in advance.

        • Re: aggregation help
          Olivier GAUTIER

          Hi

           

           

          May be try sum(total <date, scope, name>

           

          Because with your formula it adds scope of other names

           

          Regards

            • Re: aggregation help
              Mahitha M

              Hi ogautier62,

               

              Thanks for your reply.

               

              When I have applied sum(total <date, scope, name> in the CC1% the percentage values are reducing.

               

                • Re: aggregation help
                  MATEUSZ TUCHOLSKI

                  Hi,

                  there is something wrong with denomiator. Have you tried this?

                   

                  CC1% :

                  Sum(TOTAL <SCOPE>

                  Aggr(

                    RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

                  , Date, SCOPE, Name )

                  )

                  /

                  Sum(TOTAL <Name>

                  Aggr(

                    RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

                  , Date, SCOPE, Name )

                  )

                   

                  CC2%:

                  Sum(TOTAL <SCOPE>

                  Aggr(

                    RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

                  , Date, SCOPE, Name )

                  )

                  /

                  Sum(TOTAL <Name>

                  Aggr(

                    RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

                  , Date, SCOPE, Name )

                  )