10 Replies Latest reply on Jun 12, 2018 10:49 AM by waqar ahmad

    Help Please: Total Sum Not accurate

    waqar ahmad

      Hi everyone,

       

      I am having some trouble with sum calculations within my Tableau Report. The "Days Blocked" is a custom field built as a sum of the "Totaldurationdays" field.. I'm not sure why the sum is not calculating properly. I've highlighted in Yellow an example of the issue. I'm not sure where the 792 is coming from, and id expect to see 113 on both rows here as an example. The last field in which we see the total sums is the result of SUM(Days Blocked) currently on Text Mark. Any help with this is greatly appreciated. The Tab which I'm using to see this issue is the "Blocked Durations" Tab.

      I've also attached my workbook.

       

      Thank you for your help on this matter. Id really appreciate an explanation of the solution as well as I'm very much new still at Tableau.

       

      Best,

      W

        • 1. Re: Help Please: Total Sum Not accurate
          Mavis Liu

          Hi Waqar,

           

          This is because for each row for Denova Steven, where blocked end date is empty, there are 8 rows of data associated to this:

           

          2018-06-11_16h08_47.png

           

          So 99 multiplied by 8 is 792.

           

          The 99 is just a dimension you've got on as a label, it's not being summed or averaged etc. Whereas your total is SUMMING all the rows.

           

          Please could you explain how you want the calculation to work? Because I see that card id isn't unique for each person. e.g. 638788919 is associated with 2 different people. Users such as Steven DeNova was 4 cardids associated with him.

           

          So how do you want the totals to be added up? Is it for each cardid? For each user?

           

          Thanks,

           

          Mavis

          1 of 1 people found this helpful
          • 2. Re: Help Please: Total Sum Not accurate
            suman kumar

            HI

             

            I have gone through your workbook and find out backend calculation for value 112 and just after that 792.

            Here is screen shot.

            Screen Shot 2018-06-11 at 8.47.46 PM.png

            Here you can see that 14 is value of Days Blocked and

            Below is screen shot of 792

             

            Screen Shot 2018-06-11 at 8.48.20 PM.png

             

            Here Days Blocked value is 99. and if data is 99 then the value is 792 correct.

             

            can you please check your data first at least once..?

             

            Thanks

            Suman

            • 3. Re: Help Please: Total Sum Not accurate
              waqar ahmad

              Hi Mavis!

               

              Thank you for that detailed explanation. I can concur with you as when I turn my "Days Blocked" from a Sum to a Count, I'm seeing the same row count of data you're referring to! Thanks for this insight. As far as how Id' like to see them calculated, Since Steve Denova can be on Multiple cards, Id like to see total duration days for each card regardless of the name .. Since total duration days is currently showing me the proper number for each name.

               

              So in our example above, Id' like to see

               

               

              Thanks a lot!

              W

              • 4. Re: Help Please: Total Sum Not accurate
                Mavis Liu

                Hi Waqar,

                 

                Please can you use this calculation? (please note that Total duration days 2 is just the integer version of total duration)  -

                {FIXED  [Cardid (Card Blocked History)] : SUM(

                 

                 

                {FIXED [Cardid (Card Blocked History)], [Blockedbyuserfullname], [Blockedenddate] : max([Total duration days 2])})}

                 

                The above is a fixed LOD calcualtion, where I'm working out the maximum value per cardid, user, and end date, then on a cardid level I am summing those values:

                 

                2018-06-11_16h29_28.png

                 

                I've also attached the workbook if you want to take a look.

                 

                Please note that a fixed LOD calculation works before any filters in Tableau's order of operations: Tableau's Order of Operations

                If you want any filters to take into affect before the calculation, then you can right click on the filter and select 'add to context'.

                Or you can use a different type of LOD, such as include or exclude which comes after filters.

                 

                More details on LODs here:  Overview: Level of Detail Expressions and Top 15 Tableau LOD Expressions (Practical Examples)

                 

                Please

                 

                Thanks,

                 

                Mavis

                1 of 1 people found this helpful
                • 5. Re: Help Please: Total Sum Not accurate
                  Jim Dehner

                  I took a look at the data - to the extent that I could and you have a number of duplicate records in the data see below

                   

                  your data also has several date and numerical dimensions that were brought in as strings -  makes it a little to follow and work with

                   

                   

                  Don't really know what you are doing but suggest you look into the data to see what caused the duplicates

                  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
                  • 6. Re: Help Please: Total Sum Not accurate
                    waqar ahmad

                    Hi Mavis! Thank you very much for your help that did it for me! Did we have a particular reason as to why we used the Blockedenddate as opposed to the start date? I was seeing the same results when using the blockedstartdate and wasnt sure what the reason for this was.

                     

                    Thanks again for your help you're a life saver!

                     

                    Waqar

                    • 7. Re: Help Please: Total Sum Not accurate
                      waqar ahmad

                      Hi Jim,

                       

                      Thank you for your insights! I was actually just handed the report with these issues and was asked for help regarding this. I'm not sure as to the backstory of the duplicates or who brought in the data how.

                       

                      Thanks for your message!

                      W

                      • 8. Re: Help Please: Total Sum Not accurate
                        Jim Dehner

                        Glad you got a solution to work around the data issues - I would suggest to you that cleaning the data at the source is a better long term solution -

                        As is every change in the viz will result in another workaround

                         

                        Jim

                        1 of 1 people found this helpful
                        • 9. Re: Help Please: Total Sum Not accurate
                          Mavis Liu

                          No real reason, just it was one of the fields which made that row unique:). For the example based on your screenshot, either of those date fields will be fine and will ultimately give the same result.

                           

                          Thanks,

                           

                          Mavis

                          1 of 1 people found this helpful
                          • 10. Re: Help Please: Total Sum Not accurate
                            waqar ahmad

                            Awesome!! Thanks for your help and answers Mavis,

                             

                            highly appreciated