7 Replies Latest reply: Sep 23, 2015 2:14 AM by Darrin Pilkington RSS

    Odd results from the function floor()

    Darrin Pilkington

      I have values in a chart where I do not want the results to round past .0001.  I am using the Floor() function to drop values past this value. In most cases it works as I would expect but not always. 

       

      The formula being used is  floor(TotalPointsEarned/TotalPointsPossible,.0001)

       

      Notice the first record and how it takes the 7/10 = .7 and turns it into .6999 but in all other cases it works properly. 

       

         

      TotalPointsEarnedTotalPointsPossibleTotalPointsEarned/TotalPointsPossiblefloor(TotalPointsEarned/TotalPointsPossible,.0001)
      --
      7100.70.6999
      9110.8181818180.8181
      10110.9090909090.9090
      10.75120.8958333330.8958
      9.75130.750.7500

       

      Thanks for your help.

        • Re: Odd results from the function floor()
          Settu Periyasamy

          Yes you are right. In excel it gives 0.7 for Floor(0.7,0.0001).

          Check the below link..

           

          Doubts in understanding the ceil and floor functions

           

          Not sure about this. But, check this..

           

          In this case (in QV), the multiple of 0.0001 are

          0.0001

          0.0002

          ...

          ...

          0.6999

          0.7

          Capture.JPG

            • Re: Odd results from the function floor()
              Darrin Pilkington

              Settu,

              Thank you for the response.  The link to the article really helped explain what is really happening with ceil() and floor().

               

              But I still had the problem of dropping any values after the 4th position due to the team not wanting rounding past the 100th percent.

               

              At random I tried left(TotalPointsEarned/TotalPointsPossible, 6) and that dropped off the remaining values to cure the rounding.  I am really surprised this worked as I thought it was only design for text.

            • Re: Odd results from the function floor()
              Darrin Pilkington

              OK,

              Maybe I spoke too soon.  Now I am really confused.


              In a chart left(TotalPointsEarned/TotalPointsPossible, 6) gives a value of .7 but that same formula in the load script returns a value of .6999

               

              All I am trying to do is cut off the last values to the 100th place as a percent without rounding.

               

              So .8432684 would cut off the 684 leaving .8432 and then I format as 84.32%

              In addition .7 would stay .7 and finally format as 70%

               

              Is there some other function that would trim off the last values without rounding or dropping to some next closest value?

               

              Thank you.

              • Re: Odd results from the function floor()
                Darrin Pilkington

                I did try that and it rounds the value up.

                For example .82828282 becomes 82.83%

                 

                So this is interesting and odd.  I am not sure if something was being held in memory but I cleared all data from the app and then reran again with the left(TotalPointsEarned/TotalPointsPossible, 6) and it gave me the same value of .7 that the formula in chart did.

                 

                It's interesting, I searched throughout the community and did not see anything about the left() function on a number to trim off the trailing values.

                 

                Thanks again Settu