7 Replies Latest reply on Mar 20, 2018 8:20 AM by Michael Ye

    Excluding Item from Totals

    Brian Boddy

      Greetings again -

       

      Can't seem to figure out how to do this:

       

      I have a basic data set that looks like this:

      Company     # of Sales     $$ Sales

      A                         10               5000

      B                         20               7500

      C                         42               9000

      D                         30               6500

       

      I want to be able to get the column totals w/all Companies included, which is easy enough,

      But I also want to get the Totals while excluding 1 or more members - say I want column Totals for # of Sales and for $$ Sales, but excluding Company C.

       

      How do I do that?  What does that calculation look like?

       

      Thanks for any help.

      Brian.

        • 1. Re: Excluding Item from Totals
          Michael Ye

          Hi Brian,

           

          Please see the screenshot. Make two calculations which exclude C:

           

          IF FIRST()!=LAST()

          THEN SUM([# of Sales])

          ELSE SUM(IF [Company]!='C' THEN [# of Sales] END)

          END

           

           

          IF FIRST()!=LAST()

          THEN SUM([$$ Sales])

          ELSE SUM(IF [Company]!='C' THEN [$$ Sales] END)

          END

           

          The totals will exclude C.

          Hope it helps.

           

          Michael Ye

           

          • 2. Re: Excluding Item from Totals
            Brian Boddy

            Thank you very much for that help.  It gets me down the road some, but the problem is w/my example.

             

            Forgive me, I’m new to all of this, including writing the calculations.

             

            What we do is evaluate freight pricing.  So I have data that looks like:

             

            Shipment           Company           Rate

            1                           A                          200

            1                           B                          100

            1                           C                          175

            1                           D                          185

            1                           E                           225

            2                           A                          450

            2                           B                          250

            2                           D                          700

            2                           E                           375

            2                           F                           400

             

            So I might have 5 different companies who will run these shipments, but not necessarily the same 5.  But all I want to do is add up the Lowest prices on each shipment – once w/all the companies included and once w/out Company B, for example.

             

            So in the above data, I just want to return Totals of $350 (Totaling the lowest prices on each shipment w/all companies included), and then another Total showing the sum of the lowest prices on each shipment, but Excluding Company B = $550.

             

            I apologize for the lack of specificity in my earlier example.

            Any further help appreciated.

            Thanks

            Brian.

            • 3. Re: Excluding Item from Totals
              Michael Ye

              Brian,

               

              Please see the screenshot.

               

              make a calculated field: Total for Smallest rate for each Shipment:

               

              IF FIRST()!=LAST()

              THEN SUM([Rate])

              ELSE SUM({ FIXED [Shipment]:MIN([Rate])})

              END

               

              This will add rate for company B in shipment 1 and 2, totally 350 (100+250).

               

              Make another calculation:

               

              Other totals:

               

              IF FIRST()!=LAST() THEN SUM([Rate])

              ELSE (TOTAL(SUM([Rate]))-SUM({FIXED [Shipment]:MIN([Rate])}))

              END

               

               

              This is the total rate exclude that of company B in each shipment.

               

              Thanks,

               

              Michael Ye

               

               

              • 4. Re: Excluding Item from Totals
                Brian Boddy

                I’m sorry – I don’t see any differences in the 2 calculations below?

                 

                Brian K. Boddy

                SGI, LLC

                Analytics

                Brianb@storygroup.net<mailto:Brianb@storygroup.net>

                509.222.3043

                • 5. Re: Excluding Item from Totals
                  Michael Ye

                  Revised.

                   

                  First one:

                   

                  IF FIRST()!=LAST()

                  THEN SUM([Rate])

                  ELSE SUM({ FIXED [Shipment]:MIN([Rate])})

                  END

                   

                  second one:

                   

                  IF FIRST()!=LAST() THEN SUM([Rate])

                  ELSE (TOTAL(SUM([Rate]))-SUM({FIXED [Shipment]:MIN([Rate])}))

                  END

                   

                   

                  Michael Ye

                  • 6. Re: Excluding Item from Totals
                    Brian Boddy

                    Thank you!

                     

                    Brian K. Boddy

                    SGI, LLC

                    Analytics

                    Brianb@storygroup.net<mailto:Brianb@storygroup.net>

                    509.222.3043

                    • 7. Re: Excluding Item from Totals
                      Michael Ye

                      Brian,

                       

                      If it is what you need, please mark it as correct and close the thread so others can make it as references. Thanks,

                       

                      Michael Ye