4 Replies Latest reply on Jul 15, 2018 11:22 PM by jan.walischewski

    Tableau SUM is worng

    jan.walischewski

      Hey Guys,

       

      I'm new in Tableau and BI Software and started playing around. Everything is going well but i've created a sheet "Blatt 12" and Tableau is summing up my values incorrectly. Instead of a value of 49 i'm getting a value of 281,5. I'm using a filter to only display one type of values and then trying to sum these values up. I've added my package to this post. I've tried already some resulutions mentioned when you search for "tableau sum wrong" but nothing really helped out. Does anybody know what's the problem?

       

      Thanks!

        • 1. Re: Tableau SUM is worng
          Alex Kerin

          Jan, welcome to Tableau, and the forums.

           

          Your issue is that you have multiple rows in your source for each bug ID. For example, Bug ID 3261 has four rows in the source, and therefore 4 rows that contribute to the sum of DurchlaufzeitBisResolvedHour.

           

          I don't know how you want to fix this - whether you want to revisit your source (and the likely joins that created the multiple rows), use Tableau Prep to remove what you consider duplicates, or to fix this using calculations.

           

          It's entirely possible to do this with calculations, but I suspect your other calculated fields have the same issue of being thrown off by multiple rows.

           

          However, a fix for this could be a LOD calculation like:

           

          {fixed [Bug Id]:min([DurchlaufzeitBisResolvedHour])}

           

          This results in a sum of 39.5, instead of 281.5. Note this is still different than your 49 - probably because fixing DurchlaufzeitBisResolvedHour at the Bug ID level is wrong. Bug ID has different DurchlaufzeitBisResolvedHour values..

          • 2. Re: Tableau SUM is worng
            jan.walischewski

            Thanks a lot for this resolution. Yes you're right, i've validated my other calculations 'Reaktionszeit' and 'Durchlaufzeit' there both had the same issue. I've used your Calculation, and the result matches with my manually calculated result. Can you please tell me what this calculation, exactly does?
            I've already used LODs for my calculation as filters and have seen, that LODs exclude multiple rows. But what does your calculation does?

             

             

            Thanks a lot!

            • 3. Re: Tableau SUM is worng
              Alex Kerin

              It's not that LOD calcs exclude rows per se. In this case, we are saying that whatever is on the view show me the minimum value for DurchlaufzeitBisResolvedHour given a single Bug ID. So for your Blatt 12 view, give the the minimum once for each Bug ID, then sum these (due to the sum on the row pill). The LOD is not calculated for every row (unless the view needs it), so you don't fall into the same trap as before. Be careful, as LOD calcs are not filtered unless the filter is a 'context' based filter. Equally, I got different values if I used max or avg in the LOD, showing that Bug ID may not be the right way to fix the measure. Why is min the right value to use?

              • 4. Re: Tableau SUM is worng
                jan.walischewski

                Thanks for that explenation. I don't know if i understand your question right.

                The reason why min is giving the correct results, is that the time is measured a supportticket needs to be resolved. So its the timespan between status "new" (10) and "resolved" (80). I've a table where the history of every ticket is documented. I'm checking in the history if a ticket got edited in the status field. If the status was set to "80" the timespan between ticket got opened and ticket got resolved is measured with tableau. I think the reason why min and max in the LOD is giving different values is that sometimes a ticket needs to be reopened. I think in that case if a ticket gets reopend and resolved again tableau has two times. When I'm using min, tableau picks the first value when the ticket got resolved, on max tableau picks the last value and with avg tableau picks a value between. While I'm writing this MAX could be the better option, then min.

                 

                Greetings!