Changing Font Color based on a formula

user06345
user06345 Member
in Charting

I'm trying to change the color of a number and when I do DOMO reads it as text instead.  When I try to CAST it back to a number it zeroes out.

 

Here is what works but it reads as text:

concat('<span style="color:green">',(SUM(`Base_NMU`)/MAX(`Base_SalesCumm`)), '</span>')

 

Any help would be greatly appreciated

 

Best Answer

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    Unfortunately, you won't be able to get something like that unless you do your aggregations within a MySQL DataFlow or Magic ETL. Something like ... 

    select team
    ,department
    ,new_members
    ,old_members
    ,net_members
    from team_file

    union all 

    select 'Subtotal' as team
    ,department
    ,sum(new_members) as new_members
    ,sum(old_members) as old_members
    ,sum(net_members) as net_members
    from team_file
    group by department

    union all 

    select 'Total' as team
    ,'All' as department
    ,sum(new_members) as new_members
    ,sum(old_members) as old_members
    ,sum(net_members) as net_members
    from team_file

    If your grouped columns were by team and department and then you wanted a subtotal on department, and an all-in total you would write the SQL like the above. Then when it processes you can use the HTML beastmode and it won't matter if it's a text because you will already have subtotals and totals.

     

    Hope this helps,

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Property_Ninja
    Property_Ninja Contributor

    What is the end result you are trying to achieve. Could you please provide an example?

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user06345
    user06345 Member

    I'm trying to show all positive numbers that contribute to our net gain in green, net loss in red and so on

    New Member Counts = Green

    Cancellation Counts = Red

    Net Members = Green/Red depending if its a positive or negative.

  • Property_Ninja
    Property_Ninja Contributor

    Using the concat in a beastmode with html will always turn your numbers to text.

     

    Why do you need to show them as numbers?

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user06345
    user06345 Member

    These are subtotaled by region as well as having a grand total.  DOMO reads them as text so the subtotals/grand totals blank out.

  • Property_Ninja
    Property_Ninja Contributor

    Have you considered using a heat map table? This way you can control the colors of the cells if they are negative or positive, keep them as numbers, and be able to do a subtotal/grandtotal. This option wouldn't even require you to write a beastmode with HTML.

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user06345
    user06345 Member

    It doesn't give me the result/view I'm looking for.  I'm looking for something like this:Font_Color_Ex.JPG

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    Unfortunately, you won't be able to get something like that unless you do your aggregations within a MySQL DataFlow or Magic ETL. Something like ... 

    select team
    ,department
    ,new_members
    ,old_members
    ,net_members
    from team_file

    union all 

    select 'Subtotal' as team
    ,department
    ,sum(new_members) as new_members
    ,sum(old_members) as old_members
    ,sum(net_members) as net_members
    from team_file
    group by department

    union all 

    select 'Total' as team
    ,'All' as department
    ,sum(new_members) as new_members
    ,sum(old_members) as old_members
    ,sum(net_members) as net_members
    from team_file

    If your grouped columns were by team and department and then you wanted a subtotal on department, and an all-in total you would write the SQL like the above. Then when it processes you can use the HTML beastmode and it won't matter if it's a text because you will already have subtotals and totals.

     

    Hope this helps,

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user06345
    user06345 Member

    Yea that's a good point.  I appreciate the help and time on this!

This discussion has been closed.

Categories