10 Replies Latest reply on Jan 17, 2018 4:15 PM by Chris McClellan

    Wrong result displayed?

    Greg Mooney

      Using this on a map and my map label is incorrectly showing "More than 100 Trillion Dollars" when the actual answer should be anywhere from $4,149,509 to $44,799,854 dependent on which of the 10 zip codes is showing data.

       

      I then created a calculation that is returning 18 for each of the 10 zip codes mapped:

       

      len(STR([Sum Dollars]))

       

      And when I change the below in the 4th ELSEIF from 15 to 18 I get the following for a zip code that should be $4,149,509:

       

      $414950,8.8,799,999,971

       

      Every one of the 10 data points is like the above example with the first part of the result being the correct dollar amount but each has a totally different second part of the number.

       

      CASE[Choose Dollars or Units]

      WHEN "Dollars" THEN

      IF LEN(STR([Sum Dollars]))<3 THEN

          "$" + STR([Sum Dollars])

      ELSEIF LEN(STR([Sum Dollars]))<=6 THEN

          "$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-3)

          + "," + RIGHT(STR([Sum Dollars]), 3)

      ELSEIF LEN(STR([Sum Dollars]))<=9 THEN

          "$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-6)

          + "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Dollars]), 3)

      ELSEIF LEN(STR([Sum Dollars]))<=12 THEN

          "$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-9)

          + "," + MID(RIGHT(STR([Sum Dollars]), 9), 1, 3)

          + "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Dollars]), 3)

      ELSEIF LEN(STR([Sum Dollars]))<=15 THEN

          "$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-12)

          + "," + MID(RIGHT(STR([Sum Dollars]), 12), 1, 3)

          + "," + MID(RIGHT(STR([Sum Dollars]), 9), 1, 3)

          + "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Dollars]), 3)

      ELSE

            "More than 100 Trillion Dollars"

      END

      WHEN "Units" THEN

      IF LEN(STR([Sum Units]))<3 THEN

          STR([Sum Units])

      ELSEIF LEN(STR([Sum Units]))<=6 THEN

          LEFT(STR([Sum Units]), Len(STR([Sum Units]))-3)

          + "," + RIGHT(STR([Sum Units]), 3)

      ELSEIF LEN(STR([Sum Units]))<=9 THEN

          LEFT(STR([Sum Units]), Len(STR([Sum Units]))-6)

          + "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Units]), 3)

      ELSEIF LEN(STR([Sum Units]))<=12 THEN

          LEFT(STR([Sum Units]), Len(STR([Sum Units]))-9)

          + "," + MID(RIGHT(STR([Sum Units]), 9), 1, 3)

          + "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Units]), 3)

      ELSEIF LEN(STR([Sum Units]))<=15 THEN

          LEFT(STR([Sum Units]), Len(STR([Sum Units]))-12)

          + "," + MID(RIGHT(STR([Sum Units]), 12), 1, 3)

          + "," + MID(RIGHT(STR([Sum Units]), 9), 1, 3)

          + "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + "," 

          + RIGHT(STR([Sum Units]), 3)

      ELSE

          "More than 100 Trillion Units"

      END

       

       

      END