Changing Font Color based on a formula

user06345

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

 

Property_Ninja

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.

Property_Ninja

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

 

Brian

user06345

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

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

user06345

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

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.

user06345

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

Property_Ninja

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.

user06345

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