Hello there, I am having issues with a custom metric that I do not quite understand. Can I not return a "string" value here in this custom metric for some reason?

{
  "title": "Bad Request",
  "status": 400,
  "detail": "A result cache error has occurred during the calculation of the result",
  "resultId": "f365607a0253341f783d3027d573f07a76601f5a",
  "reason": "For input string: \"Fly\"",
  "traceId": "730609a0fd52af60"
}

Hi Jake, This cannot return a string value for the case statement. I can certainly mark this for product feedback on your behalf.

🎉 New note created.

Hi @Jake Marcotte while metrics can currently only return numbers, you can overcome it by using custom metric format where you can format the metric to actually display some string. And in your case, since GoodData supports conditional formatting, you can even simplify your metric significantly and get rid of the CASE statement and only calculate the numner of days:

SELECT DATETIME_DIFF(Business Start Date - Date {label/start_date.day}, this(DAY)) BY Business id {label/businessid}

and then use the conditional number formatting expression to give you the proper string value based on the number of days:

[>=270]Fly;
[>=180]Run;
[>=90]Walk;
[=null]--;
Crawl

This should get you exactly what you need. BTW if you would need to use some more complex CASE/IF statement logic, you can always return some explicit numerical values from the metric and then format those values to string in the number format.

Awesome this worked decently. A bit of a follow up but, Can I then use that calculated value in

Phase

to customize the format of a different metric? I am assuming this is not supported...

You are right that this is not supported at the moment. I have seen some creative tricks like multiply the Phase metric by some large factor (million) and add it up to the other metric and then format based on the millions and somehow only display the thousands... but that is probably not worth it and migh cause issues i.e. for exports etc.

ahh understood. I appreciate the feedback!