Forum Discussion

BRIANHINSHAW's avatar
BRIANHINSHAW
Qrew Member
3 years ago

Multiple IF or Case statement for metric presentation

Have a scorecard where the metric may be a total or an average. Based on that setting, trying to build formula rich text to show the appropriate data.  Got half of it working for Total. How would I...
Formulas and functions
BRIANHINSHAW's avatar
BRIANHINSHAW
Qrew Member
3 years ago
A QB Ninja helped clear this up for me and simplify the code.

If(IsNull([2022 Actual YTD]),"No Actuals",
Case(
[Measurement],
"Total",
"<div style='color: " & If([2022 Actual YTD] < [2022 Target Low],"#FF0000",[2022 Actual YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>",
"Average",
"<div style='color: " & If([2022 Actual AVG YTD] < [2022 Target Low],"#FF0000",[2022 Actual AVG YTD] >= [2022 Target High],"#228B22") & "'>"&If([Metric Type]="Currency","$")&ToFormattedText(Round([2022 Actual AVG YTD],0.01),"none_dot")&Case([Metric Type],"Currency","M","Percent","%")&"</div>",
ToText([2022 Actual AVG YTD])
)
)
​

------------------------------
BRIAN HINSHAW
------------------------------