7 Replies Latest reply on May 15, 2018 1:57 PM by Prakash Madhavan

    Subtract instead of Sum as Grand Total

    Gareth Jones

      Hi

      I feel this is something ridiculously simple, but currently I have:

       

      Type
      Count
      Gross Inactive100
      Gross Active150
      Grand Total250

      And what I am trying to achieve is:

       

      Type
      Count
      Gross Inactive100
      Gross Active150
      Grand Total50

       

      From what I can tell, table calculations are only useful for replacing the actual end result, not the Grand Total calculation.

       

      Am I missing something obvious?  It may be important to note that the split by type is achieved with a bin calculation.

       

      Thanks!

        • 1. Re: Subtract instead of Sum as Grand Total
          Richard Leeke

          Well I haven't tried it with bins (I wasn't quite sure what you meant), but I've attached a workbook that shows how you can display what you want just with raw numbers.

           

          The trick I used was to arrange for the inactive numbers to be negative, but just suppress the minus sign by defining a custom format mask of:

           

          #;#

           

          That just says display both +ve and -ve numbers without a sign.

          2 of 2 people found this helpful
          • 2. Re: Subtract instead of Sum as Grand Total
            Gareth Jones

            Richard,

            As always, thanks for your help!

             

            Actually, showing what I have as a negative would be even better (I wouldn't need to hide the the negative sign, I just wasnt sure how I could do it).

             

            However, I think due to how I am using the bins, this will not work (but is on the right track, I can feel it!)

             

            I'll try and explain...if you dont follow me let me know and i'll construct a demonstration package workbook.

             

            Basically, to identify the Inactive / Active, I have a bin calculation on a calculated column that goes:

             

            IF ([AUS_VOLUME_R24]+[AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]) >0

            AND ([AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]+[AUS_VOLUME_R12]) <=0

            THEN 1

            ELSEIF ([AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]+[AUS_VOLUME_R12]) >0

            AND ([AUS_VOLUME_R24]+[AUS_VOLUME_R23]+[AUS_VOLUME_R22]+[AUS_VOLUME_R21]+[AUS_VOLUME_R20]+[AUS_VOLUME_R19]+[AUS_VOLUME_R18]+[AUS_VOLUME_R17]+[AUS_VOLUME_R16]+[AUS_VOLUME_R15]+[AUS_VOLUME_R14]+[AUS_VOLUME_R13]) <=0

            THEN 2

            ELSE 0

            END

             

            Each column is a previous month's total transactions (the [AUS_VOLUME] etc.)

            1 will equal a customer that was previously inactive, but is now inactive

            2 will equal a customer that was previously active, but is now inactive

            0 will equal all others that did not move

             

            Then I am using bins to show the 0, 1, 2 and just renaming the label, excluding 0 (as I am just trying to see my "net movements") and then doing counts in my data based on customer ID.

             

            This has been the most effective and the fastest way I have found so far to use my large calculated fields when it comes to splitting by history.

             

            Soooo....am I screwed? 

            • 3. Re: Subtract instead of Sum as Grand Total
              Gareth Jones

              You know what, I have only just figured out how to show the table calculation as well as the actual values on the table calculation.

               

              I think I can get around it with that - ill see what I can work out.  It's not a major if the totals are on the right.

               

              EDIT:

               

              Yeah, I found a rather "rough" way to get around it with some smart labeling.

               

              Not the best, but it does the job!

              • 4. Re: Subtract instead of Sum as Grand Total
                Richard Leeke

                I only glanced at it - but if you use -1 for "has become inactive", 0 for "no movement" and 1 for "has become active" doesn't it just do exactly what you want?

                • 5. Re: Subtract instead of Sum as Grand Total
                  Gareth Jones

                  Yup it would if I was summing the outcome - but I'm using bins instead, and counts on the bins.

                   

                  It's all good, I worked around it with some crazy formatting

                  • 6. Re: Subtract instead of Sum as Grand Total
                    Richard Leeke

                    I think you could just have two calculated fields - one that gives the active/inactive classification as you have at present) and one that gives the value 1 or -1 and then just use sum() instead of count.

                     

                    But if you're happy with your crazy formatting that's fine...

                    • 7. Re: Subtract instead of Sum as Grand Total
                      Prakash Madhavan

                      Thanks Richard, your solution was so helpful after hours of searching