4 Replies Latest reply: Aug 10, 2018 4:09 AM by Marina Dor RSS

    Total issue

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below issue.

      In the below table I have calculated CC1 measure using below expression.

      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

       

      CC1 total is 167,947,700 but getting Auto total as 120,672,758

      The requirement is  I have to create the second table in the app with Name and Scope as dimension and CC1 as Measure but getting all wrong totals.

      .

      Please help me on this.

       

       

      Thanks in advance.

        • Re: Total issue
          Mahitha M

          Hi,

           

          Could you please help me on above issue.

           

          stalwar1

           

          jyothish8807

           

          youssefbelloum

          • Re: Total issue
            Marina Dor

            Hi Mahitha,

             

            The total CC1 gives you bad result because the total isn't the sum the different values found for CC1, but is recalculated on the total data.

            It does :

             

            RangeMax(0,Sum(Value)) +


            Sum(Value) here =

            48906566

             

             

            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

            Sum(Item1) here =

            16410074

            Sum(Item2) here =

            28795310

            etc...

             

            Then does RangeMax on Sum(Item1), Sum(Item2)... /5 + 0,1, here =

            71766192,1

             

            Then sum 48906566 and 71766192,1, which =

            120672758

             

            You should try aggregating by the choosen dimensions.

            Hope it helps,

             

            Marina

            • Re: Total issue
              Ruben Marin

              Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:

              Sum(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, Name, SCOPE))


              In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)