5 Replies Latest reply on Apr 6, 2018 8:00 AM by Nick Dwyer

    Cannot Sum Aggregate

    Nick Dwyer

      Hi All

      This is my first post to the Tableau community forums, so please excuse if this has already been answered. I'm having some problems in Tableau 10.

      I'm trying to basically create a custom total column which sums the given "points" for performing a task, but I seem to be stopped by the pesky cannot further aggregate SUM as it's already an aggregate message. Here's the formula I have to calculate the points for the task.

       

      COUNT([Ticket])*SUM([Point Allotment])

      Here's a screenshot of what I'm working with, unfortunately my company DLP solution prevents me from uploading files to external sites.

       

       

      What i'd like to do, for example on 3/12, is sum the 220, 55, 41.3, and 220, but when I do the subtotal function I get the following:

       

        • 1. Re: Cannot Sum Aggregate
          Jim Dehner

          Hi Nick

          Welcome to the forum

           

          this is use a simple sales / order calculation

           

          like your issue if I just drag it on the viz and use grand total I get this

           

           

          but follow the path to sum and you get this

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Cannot Sum Aggregate
            Zhouyi Zhang

            Hi, Nick

             

            Your calculation is somehow misleading tableau to calculate in different way which is count(tickets from BET + tickets from disconnect+ ....) * sum(points from BET+ points from disconnect+ .....),

            while in your expected result is count(ticket from bet)*sum(point from bet) + count(ticket from disconnect)*sum(point from disconnect) + ....

             

            So my solution is using superstore as example to mock up your situation and give the correct calculation. Below is the screenshot for your reference and a sample workbook attached as well.

             

            Hope this helps

             

            ZZ

            • 3. Re: Cannot Sum Aggregate
              Nick Dwyer

              Thank you Zhouyi, we're almost there. I think I should have mentioned i'm using two data sources, so I have the calculation as follows, but the error I'm getting now is "All fields in a LOD expression must come from the same data source"

               

              {fixed DAY([Completion_Date__c]),[Source2__c]:COUNTD([EBA_Ticket])*SUM([Goals (Production v6)].[Point Allotment])}

              • 4. Re: Cannot Sum Aggregate
                Zhouyi Zhang

                Hi, Nick

                 

                In this case, have you tried Jim's solution?

                 

                ZZ

                • 5. Re: Cannot Sum Aggregate
                  Nick Dwyer

                  Hi Jim,
                  This may be a simple solution, but here's what I'm seeing per your guidance

                   

                  Thanks

                  Nick