Forum Discussion

PatriciaMalarke's avatar
PatriciaMalarke
Qrew Trainee
9 months ago

Color-coding a field based on calculated percent

Hi, All!

I am trying to color-code a calculated field.  It's a percentage field:  Count divided by Number Held. 

I stole this formula and adapted it from another post, but can't seem to find it again to put the link in this post.

If(
[Count]/[Number Held] > 0.1,
"<div style=background-color:Black>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] > 0.02,
"<div style=background-color:Red>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] > 0,
"<div style=background-color:Yellow>" & ToText([Count]/[Number Held]) & "</div>",

[Count]/[Number Held] <= 0,
"<div style=background-color:LightGreen>" & ToText([Count]/[Number Held]) & "</div>",

ToText([Count]/[Number Held]))

This gets me most of the way there.  But I'd like to display ToText([Count]/[Number Held]) as a percent (desired) or, at the very least, multiply it by 100 and round to 2 decimal places.  Multiplying by 100 doesn't seem to change the result and rounding gets me all kinds of error messages.

Desired result attached.

Thanks in advance!



------------------------------
Patricia Malarkey
------------------------------

4 Replies

Sort By
  • RyanPflederer3's avatar
    RyanPflederer3
    Qrew Cadet
    9 months ago

    Hi Patricia!

    I believe this is what you're looking for: Round(([Count]/[Number Held])*100,0.01)

    Also, in an effort to save time and typing, here's the full formula leveraging variables to reduce duplicative work:

    var number value = Round(([Count]/[Number Held])*100,0.01);
    var text display = $value & "%";

    If(
        $value > 10,
            "<div style=background-color:Black>" & $display & "</div>",

        $value > 2,
            "<div style=background-color:Red>" & $display & "</div>",

        $value > 0,
            "<div style=background-color:Yellow>" & $display & "</div>",

        $value <= 0,
            "<div style=background-color:LightGreen>" & $display & "</div>",

        $display
    )



    ------------------------------
    Ryan Pflederer
    ------------------------------

    • PatriciaMalarke's avatar
      PatriciaMalarke
      Qrew Trainee
      9 months ago

      Hi, Ryan!

      Thanks for your reply.

      I tried it as you suggested - I didn't know about the variables and that makes it so easy! - but on the third line of the IF statement, I get "Expecting number but found text".

      I've tried it the hard way:

      If(
      Round(([Count]/[Number Held])*100,0.01) > 10,
      "<div style=background-color:Black>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

      Round(([Count]/[Number Held])*100,0.01) > 2,
      "<div style=background-color:Red>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

      Round(([Count]/[Number Held])*100,0.01) > 0,
      "<div style=background-color:Yellow>" & ToText(Round(([Count]/[Number   Held])*100,0.01)) & "%" & "</div>",

      Round(([Count]/[Number Held])*100,0.01) <= 0,
      "<div style=background-color:LightGreen>" & ToText(Round(([Count]/[Number Held])*100,0.01)) & "%" & "</div>",

      ToText(Round(([Count]/[Number Held])*100,0.01)))

      And the easy way:

      var number value = Round(([Count]/[Number Held])*100,0.01);
      var text display = $value & "%";

      If(
          $value > 10,
              "<div style=background-color:Black>" & $display & "</div>",

          $value > 2,
              "<div style=background-color:Red>" & $display & "</div>",

          $value > 0,
              "<div style=background-color:Yellow>" & $display & "</div>",

          $value <= 0,
              "<div style=background-color:LightGreen>" & $display & "</div>",

          $display
      )

      No joy.  :-(



      ------------------------------
      Patricia Malarkey
      ------------------------------
      • RyanPflederer3's avatar
        RyanPflederer3
        Qrew Cadet
        9 months ago

        Hmmm, can you verify that both Count and Number Held are numeric fields? It worked on my side when both are set as numeric fields so I'm not sure what else would be causing the error you're seeing.



        ------------------------------
        Ryan Pflederer
        ------------------------------