12 Replies Latest reply: Oct 13, 2015 4:28 AM by Dafnis X RSS

    Calculating total in a straight table

    Dafnis X

      Hi,

      I'm trying to achieve the following:

      I have a metric: Target = Quantity - Diff

      In a straight table,  I would like to display one row for a country, which would be identical

      to the Target displayed in the top table (Straight Table - with Branches), which is based on the sum of rows

      But, I receive total which is calculated as the difference of the total sums instead.

       

      How can this calculation be achieved?

       

      Straight Table - with Branches

       

       

       

      Branch

      Quantity

      Diff

      Target

       

      5311

      2903

      4608

      A

      1739

      274

      1465

      B

      1595

      252

      1343

      C

      1977

      177

      1800

      Straight Table - with Branches - Wanted

      Country

      Quantity

      Diff

      Target

      France

      5311

      2903

      4608

      Straight Table - with Branches - Received

      Country

      Quantity

      Diff

      Target

      France

      5311

      2903

      2408

       

      Thanks!

        • Re: Calculating total in a straight table
          Bunny V

          Hi Dafnis ,

           

          Can You please attach a sample app or explain elaborately please.

           

          thanks ,

          Bunny

            • Re: Calculating total in a straight table
              Adam Krepistman

              In the straight tablw with branches, the total, based on your statement of  Target = Quantity - Diff- diff is not correct (5311 - 2903 does not equal 4608)

               

              Do not understrand straight table wanted - it has the same totals as the first example which do not appear to add correctly

               

              In the 3rd example ...-Received, the math is correct based on your statement of Target = Quantity - Diff

              (5311-2903 = 2408)

               

              it appears the totals of the quanity and target are the sum of the details, but that does not appear to be the case for diff

               

              branches a,,b, c in france?

              can it be a pivot table with country and branch - when all collapsed it would like like a straight table but you would be able to get branch level numbers

            • Re: Calculating total in a straight table
              Miguel Braga

              The Total Diff Value in the first table isn't right! Can you please tell us what's the diference between Wanted and Received?

              • Re: Calculating total in a straight table
                Adam Krepistman

                In the straight tablw with branches, the total, based on your statement of  Target = Quantity - Diff- diff is not correct (5311 - 2903 does not equal 4608)

                 

                Do not understrand straight table wanted - it has the same totals as the first example which do not appear to add correctly

                 

                In the 3rd example ...-Received, the math is correct based on your statement of Target = Quantity - Diff

                (5311-2903 = 2408)

                 

                it appears the totals of the quanity and target are the sum of the details, but that does not appear to be the case for diff

                 

                branches a,,b, c in france?

                can it be a pivot table with country and branch - when all collapsed it would like like a straight table but you would be able to get branch level numbers

                • Re: Calculating total in a straight table
                  Dafnis X

                  Hi All,

                   

                  Thank you all for your kind help.

                   

                  Please note that i had to simulate a very simplified example based on complex expressions.

                  The wrong Diff total in the first table was received because the expression's Total Mode

                  was defined as the Expression Total. 

                   

                   

                  Staright Table - with Branches

                   

                   

                   

                  Branch

                  Quantity

                  Diff

                  Target

                   

                  5311

                  2903

                  4608

                  A

                  1739

                  274

                  1465

                  B

                  1595

                  252

                  1343

                  C

                  1977

                  177

                  1800

                   

                  When changing to Sum of rows, it would display correct.

                   

                  But, the desired metric is the Target. When I changed to Pivot, the totals still displayed wrong.

                  Staright Table - with Branches - Received

                  Country

                  Quantity

                  Diff

                  Target

                  France

                  5311

                  2903

                  2408

                   

                  I will try to generate an example app to clarify.

                    • Re: Calculating total in a straight table
                      Adam Krepistman

                      Still not sure I undersatnd - in your last example

                      the target of 2408 is correct based on the data (5311-2903)?

                       

                      the straight table with branches in your last example has the same total numbers but the detail for diff do not add up to the total

                       

                      You first examples says you want a target of 4608 - is that correct?  If that is the case, the issue is the total of the diff column is incorrect but the detail is correct.  By subtracting the detail diff from quantioty, the correct target is calcualted -diff total should then be 703

                       

                      vcan you send example with correct diff numbers?

                        • Re: Calculating total in a straight table
                          Dafnis X

                          Hi Adam,

                          In a straight table, where the country and country are dimensions, the total target  value is

                          What we'd like it to be:

                          Quant.

                          Diff

                          Target

                          5311

                          703

                          4,608

                          1739

                          274

                          1,465

                          1595

                          252

                          1,343

                          1977

                          177

                          1,800

                           

                           

                          When I change the same table to pivot, the Diff total is wrong,  as you mentioned, and  so is the target:

                          Quant.

                          Diff

                          Target

                          5311

                          2903

                          2,408

                          1739

                          274

                          1,465

                          1595

                          252

                          1,343

                          1977

                          177

                          1,800

                           

                          What am i missing here?

                           

                          Thanks!

                            • Re: Calculating total in a straight table
                              Digvijay Singh

                              Can you share the expression used for Diff and target, I think that is making all the difference.

                              • Re: Calculating total in a straight table
                                Jonathan Dienst

                                >>The wrong Diff total in the first table was received because he expression's Total Mode was defined as the expression Total.

                                >> When changing to Sum of rows, it would display correct.

                                 

                                A pivot table always calculates the expression total and not a sum of rows. To get sum of rows in a pivot, you need to use a Sum(Aggr(.....)) expression, using this syntax:

                                 

                                     =Sum(Aggr( ... your expression here ..., <chart dimension1>, <chart dimension 2>, ...)

                                 

                                The chart dimensions are ALL the fields used as chart dimensions and they must all be included in the Aggr()

                                dimensions. Example:


                                     =Sum(Aggr(Sum(Amount), Country, Branch))



                                    


                            • Re: Calculating total in a straight table
                              Srikanth P

                              I believe, your simulation have different set off results on Country Level.

                               

                              Please post sample data.

                            • Re: Calculating total in a straight table
                              Dafnis X

                              Thanks Jonathan!