5 Replies Latest reply on Apr 9, 2013 6:37 PM by Joshua Milligan

    Rounding error introduced in v8?

    David Edwards

      I'm trying to create a simple sentence in a calculated field using another calculation, and it is giving me very strange rounding errors.

       

      I recreated the same workbook in v7, and it has no problems with the rounding.

       

      I'm charting survey data, so there are basically 3 measures

      "Total" the total number of responses to this question

      "Count" the number of responses that answered 'this' response

      "Missing" the number of respondents that left the question blank.

       

      There is a calculation to figure the number and percentage of responses left blank:

       

      c_missing_as_%of_Total =

         missing / (missing + total) * 100

       

      In the calculation that constructs the sentence, I refer to this field

       

      v_missing_variable_text =

      if [Charts with "Missing" responses]

        then str([missing]) + " (" +

      str(round([c_missing_as_%of_Total],1))

      +" %) students did not respond to this question"

      end

       

       

      I have tried many combinations of rounding and casting to a string, in one, the other or BOTH calculations.

        However, I keep getting values returned like this:
      13105 (16.700000762939453%) of students did not respond to this question

       

      I have tried to reproduce the issue from a fresh workbook using the dataset, but so far no luck.

      I'm attaching the workbook which is observing this issue.

      Also, here is the dataset that I'm using before an extract was created, maybe someone can figure out how to reproduce it.

       

      Thanks!

      ~Dave

        • 1. Re: Rounding error introduced in v8?
          Dan Huff

          David--

           

          What you have run into here, even though it doesn't seem like it, is how Round should have been behaving in V7. A lot of the strange behaviors of Round allowed calculations like this to work in V7 when they really shouldn't.

           

          The good news for you (and everyone else for that matter), is that these types of tedious string calculations are no longer needed. Due to the change in how labels work in V8--namely the fact that you can have multiple fields on label--you can do this so much easier.

           

          I have attached a workbook which I believe achieves the results for which you are looking. First, I edited both the missing and the percentage calcs to take into account the Set Charts with "Missing" responses. Second, I added my percentage and missing calcs to the label shelf. I then simply clicked on the Abc icon on the marks card and then the "..." icon to edit the label. From here, you can see what I mean. Now instead of having to rely upon the slow string calcs, you can just add multiple fields into this and format it as you please. Finally, I repeated the same sentence I used in the label in the tool tip to achieve consistency across both.

           

          Hope this helps,

           

          Dan

          • 2. Re: Rounding error introduced in v8?
            David Edwards

            Dan,

             

                 Thank you very much for your quick response.

              I created a new sheet which shows that even your rounded calculation is returning a large number of decimal places despite specifying only one digit. (Dan's Calculations)

             

            I noticed you are a T4 engineer, so I am hoping you can give me a little more technical background on why this is happening. I could see it being an issue with some data sources as Robert sometimes mentions, but this is coming out of a TDE.

             

             

            I reviewed your solution, and for my purposes I still think that I need to use a string calculation for these reasons:

             

            1) Not all 'chart types' have "missing" data, and I ONLY want to show the sentence about missing data if there is some.  (I don't want something like this in the tooltip:  (%) students did not respond) -- see the new Text sheet

             

              This is the reason that the set exists, but the sample data I included originally did not have any of these. I have altered the data so that it is more clear.

             

            2) I cannot use the label shelf, as only the bar chart will be included in the final product. I included the text worksheet to demonstrate the problem across all the data at once, instead of people responding having to hover over individual bars to inspect the mis-rounded values

             

            3) I am still quite confused why in the tooltip as it is presented currently, the two calculations are returning different results, even though they have an (identical?) formula.  I can't help but believe this is not intended behavior.

            tooltip discrepency.png

             

            Attached is an updated version of the workbook that includes one "chart type" with no missing responses (Distance pref)

             

            Thanks!

            ~Dave

            • 3. Re: Rounding error introduced in v8?
              Dan Huff

              Attached is a solution that I think addresses your points. You can still get it so that the Missing style information on the labels and tooltips do not show up while also avoiding all of the string calcs. This is done by the left paren and students calculations I have added.

               

              These basically check if missing is greater than 0 for a point in the table. If it is, then add the sentences and left parentheses to the tooltip. If it is not, do not show anything. The error you are seeing here is caused by wrapping the ROUND function with a STR. In V7, this rounding was retained even though it shouldn't have been. The behavior we see now is the correct behavior.

               

              Please let me know if the attached workbook helps to solve the issue. Also, to fix the crosstab all you have to do is right click on the fields on the measure values shelf, click format, and change the formatting to Number (Custom) with a single decimal point.

               

              Thanks,

               

              Dan

              • 4. Re: Rounding error introduced in v8?
                Jonathan Drummey

                Hi Dan,

                 

                I ran into a variation of this issue a few weeks ago on v7 because STR(ROUND()) was returning different results depending on the aggregation and whether it was in an extract or not. Then it gets even more complicated with v8.

                 

                I have a question about your statement, "What you have run into here, even though it doesn't seem like it, is how Round should have been behaving in V7. A lot of the strange behaviors of Round allowed calculations like this to work in V7 when they really shouldn't." My interpretation of your statement is that something like STR(ROUND(MIN(1),1)) *should* be returning 1.0000000000000011, or 0.9999999999999998, and that doesn't make sense to me. Can you explain more?

                 

                Also, because a variety of issues, I'm still on v7 and can't use the number formatting because I'm showing % and decimal values in the same field. The suggested workaround from Tableau tech support for this case is to test the string to see if it's got a bunch of 9's in the middle, then deal with that. I've attached the workbook tech support and I exchanged.

                 

                Jonathan

                • 5. Re: Rounding error introduced in v8?
                  Joshua Milligan

                  Jonathan Drummey wrote:

                   

                  I have a question about your statement, "What you have run into here, even though it doesn't seem like it, is how Round should have been behaving in V7. A lot of the strange behaviors of Round allowed calculations like this to work in V7 when they really shouldn't." My interpretation of your statement is that something like STR(ROUND(MIN(1),1)) *should* be returning 1.0000000000000011, or 0.9999999999999998, and that doesn't make sense to me. Can you explain more?

                   

                   

                  Dan,

                  I was curious to know the answer too.

                   

                  Regards,

                  Joshua