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 expand to support Measurement being an Average or another measurement type?

If([Measurement] ="Total",

If ([2022 Actual YTD] < [2022 Target Low],
Case([Metric Type],
"Currency", "<span style='color: #FF0000'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>",
"Percent", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>",
"Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>",
""),

If ([2022 Actual YTD] >= [2022 Target High],
Case([Metric Type],
"Currency", "<span style='color: #228B22'>"&"$"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"M"&"</span>",
"Percent", "<span style='color: #228B22'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"comma_dot")&"%"&"</span>",
"Number", "<span style='color: #FF0000'>"&ToFormattedText(Round([2022 Actual YTD],0.01),"none_dot")&"</span>",
""),

//otherwise
"No Actuals"))
)

------------------------------
BRIAN HINSHAW
------------------------------
Formulas and functions

1 Reply

Sort By
  • 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
    ------------------------------