3 Replies Latest reply on Mar 2, 2017 2:47 AM by paolo.raia

    Sum figures after decimal

    paolo.raia

      Hi,

       

      So I have the following example data:

       

      ID...HOURS (decimal type)

      A    2.2

      A    3.75

      A    3.5

       

      I am using this calculation to sum the integer portion of each value:

      {FIXED [ID] : INT(SUM([HOURS]))}

       

      What I also want to do is sum up figures AFTER the decimal point only.  So the total should be 82.

       

      I have tried STR/SUM/MID/FIND variations but cannot get this to work.

       

      The reason for doing this is that I have to then convert that 82 into "hours/minutes" as the hours are currently decimalised, i.e. 30 minutes = .50.

       

      Thanks in advance.

        • 1. Re: Sum figures after decimal
          Joe Oppelt

          My first thought was to convert it to string, search for the decimal point, and grab what comes after it.

           

          But there is the INT() function, which essentially truncates off the decimal part.  So INT(2.2) results in 2.

           

          Therefore you can have a calc that does:

           

          [ID Hours] - INT([ID hours])

           

          the result for 2.2 would result in .2.

           

          So that doesn't return 75 (if the input value was 3.75), but you can multiply the result by 100 to give you what you need.

           

          Numeric manipulation is more efficient (and often easier) than string manipulation in Tableau, so I would go with the INT() approach.

          • 2. Re: Sum figures after decimal
            Jamieson Christian

            Paolo,

             

            First, a philosophical question: do you really want 82? If you SUM the HOURS together, you get 9.45. Your first calculation isolates the 9 — I would expect that you would want to use the remaining 0.45 to compute your minutes. Wouldn't you?

             

            To find the minutes portion of the total (the 0.45 in this example), use the following formula:

             

            [Minutes portion total]

            { FIXED [ID] : INT(SUM[HOURS] - INT(SUM([HOURS])) * 60) }
            

             

             

            If you really want to add up the minutes from the individual rows (the 0.82 that you asked for), the formula would look like this:

             

            [Minutes portion all rows]

            { FIXED [ID] : INT(SUM([HOURS] - INT([HOURS])) * 60) }
            

             

             

            I hope this helps.

            • 3. Re: Sum figures after decimal
              paolo.raia

              Thank you both Joe and Jamieson for your replies and feedback.

               

              I modified your example calculations to suit my needs:-

               

              STR({FIXED [ID] : STR(INT(SUM([HOURS])))})+"h "+

              STR(INT(({FIXED [ID] : SUM([HOURS])}-

              {FIXED [ID] : INT(SUM([HOURS]))})*60))+"m"

               

               

              Thank you both for pointing me in the right direction.

               

              Best regards.