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

# Help Please: Total Sum Not accurate

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

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:

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

HI

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

Here is screen shot.

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

Below is screen shot of 792

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

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

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:

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

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

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

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

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

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

Awesome!! Thanks for your help and answers Mavis,

highly appreciated