8 Replies Latest reply on Jan 17, 2018 7:57 AM by Jim Dehner

    Calculating total by day, and then by person

    Mike Dragga

      Hello,

       

      I've attached a very small and basic workbook that indicates approximately how far I've made it on this, but I need some help getting two total numbers from my data so I can use it to calculate percentages.

       

      Our agents take calls and disposition them over the course of the day. Not everyone works every day, or the same number of hours in a day, but I fine with that day being blank if they didn't work or take any calls on that particular date. The Dispo Count indicates how many of their calls that day they dispositioned using that code.

       

      I need help totaling the number of dispositions per day across all agents (so for Jan 10 it should return 847 and for Jan 11 it should return 1168) and I also need to get a total number of dispositions per day per agent (so for 1/10 Ashley had 96, Bill had 105, etc)

       

      I feel like Tableau should allow me to get to those totals, I'm just not sure how! I could restructure my query returning this data to give me a raw count of calls dispositioned and calculate the total the number of calls per code per day per agent in Tableau, but that didn't seem to get me any closer to the other total values I'm looking for.

       

      Thanks for any assistance!

        • 1. Re: Calculating total by day, and then by person
          Graeme Ord

          Hi

          You could create a calculated field that excludes Name from the calculation:

           

          {EXCLUDE [Name] : SUM([Dispo Count]) }

           

          So then you can see the total for the day and also the total by Agent

           

          Is that what you are after?

           

          Kind Regards,

           

          Graeme

          • 2. Re: Calculating total by day, and then by person
            Jim Dehner

            Hi Mike

            I'm a little confused about where the issue is

             

            see your workbook attached

             

            This is nothing but using the grand totals and subtotals in the Analytics tab

             

             

            or you can get the subtotals and details

             

             

            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.

            • 3. Re: Calculating total by day, and then by person
              Mike Dragga

              Hey guys,

               

              Thank you both for the input! Both posts are helpful, but neither actually gets me quite what I need. I need calculations (unless I can somehow do this using the total/subtotal from the Analysis menu?) that return those values as numbers for me that I can use for further calculations. I need to get to percentages of dispositions per day and then per week, so for instance where Mike had a total of 78 on 1/10 I need to be able to divide the specific dispositions by that total (A% = 13/78 = 16.66%, B% = 3/78 = 3.85%, etc) I also need the weekly total of dispositions by code (per agent) and then just the total dispositions per agent regardless of code so I can determine the weekly percentages.

               

              If it would be easier (or better) I can modify my query to return 1 for every dispositioned call and let Tableau handle all the counting and summing and totaling, I'm just not sure how to attack it from that angle either.

               

              I can try to get a better sample workbook if that would help?

               

              Thanks!

              • 4. Re: Calculating total by day, and then by person
                Graeme Ord

                If you provide a workbook of some of the examples you want, be happy to take a look

                • 5. Re: Calculating total by day, and then by person
                  Mike Dragga

                  Here is a workbook with a larger dataset where I can hopefully explain what I'm after.

                   

                   

                  I want to see the percentage value for each disposition per agent per day (and then per week) so I need to be able to total the number of dispositions they have on any given day to calculate that. The image above is my end goal in a daily view (with all days showing % values), and then as mentioned I also want to be able to do the same thing in a weekly view.

                   

                  It's entirely possible that this can't be done in Tableau and I need to connect to a second query for that total. I just feel like there has to be a way to total the columns and have it as a usable number within the worksheet.

                  • 6. Re: Calculating total by day, and then by person
                    Jim Dehner

                    Hi

                    see the attached

                    this will give you the total by day by person - it becomes the divisor in the percent calcualtions

                    this is the % calcualtion

                     

                     

                    On a daily basis they return this

                     

                    if you just look at weeks it looks like 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.

                    1 of 1 people found this helpful
                    • 7. Re: Calculating total by day, and then by person
                      Mike Dragga

                      Thank you so much for that Jim! I didn't have much time to look at this yesterday, but that is exactly what I needed for a daily calculation.

                       

                      However, when I apply the formula to the weekly view on my data (or even on the sample data) the percentages for the weekly view are adding up to more than 100% for all employees. Specifically referring to the first sample workbook I provided (and the last one you uploaded with formulas) Andrea's weekly percentages add up to 105.24%, Mike adds up to 109.48%, Joe adds up to 100.01%, etc.

                       

                      Do you have any insight into why that would be happening or what needs to change in the calculation to allow the weekly percentages to calculate properly?

                      • 8. Re: Calculating total by day, and then by person
                        Jim Dehner

                        Sorry Mike

                         

                        found the issue

                         

                         

                        and this will return

                         

                         

                        the work book is attached

                        Jim

                        1 of 1 people found this helpful