https://www.gooddata.com/ logo
Join Slack
Powered by
hello, I have a simple metric that seems hard to ...
# gd-beginners
j
hello, I have a simple metric that seems hard to create. I have a date field in a dataset (not being used as a key) and I want a metric that shows the earliest date that the event occurred. Seems like a simple
Copy code
SELECT MIN({activity_date})
But even though the metric builder allows the syntax I get errors. The fact that GoodData’s cloud interface doesn’t have a format option to allow a metric to be a date I’m worrying something really obvious is being missed? More details in thread
Copy code
{
  "title": "Bad Request",
  "status": 400,
  "detail": "A result cache error has occurred during the calculation of the result",
  "resultId": "92368acdbdcc702a695bf94ea8d2f816c28d9a3c",
  "reason": "Fact 'fact/activity_date_attend/storypark-staging' must have only numeric data type",
  "traceId": "8bdaf9d08739a8d0497eca1fc08abc93"
}
error when I want to use the metric in a visual Seems like it is telling me it doesn’t like dates?
I know the product has a pretty weird attitude towards dates being special but it seems to me you actively don’t want me to be able to add a date field and not connect it up to your main date filter.
I’m guessing I can use the activity_date field that is used as the key and is linked to the main filter in the data model - this is one of many transactional tables that all contain activity_date as a field and are all linked together via that key.
If I use
Copy code
SELECT MIN({label/ACTIVITY_DATE.day})
how do I ensure it only applies in the relevant context of when it exists on just one of the tables and not across all dates
I have tried to use the main activity date with the using function
Copy code
SELECT MIN({label/ACTIVITY_DATE.day}) USING {dataset/ROUTINES_ATTENDANCE}
and got
Copy code
"title": "Bad Request",
	"status": 400,
	"detail": "A result cache error has occurred during the calculation of the result",
	"resultId": "59c4900a8a16859d8091b822cb3efa4b88f3e431",
	"reason": "Top level metric return type=date not allowed",
	"traceId": "9b5f42b3852b725a51ae664666b6c481"
so I’m basically stuck now - any help appreciated
m
Hello Jamie, no worries, nothing obvious is being missed, from the logs I see “activity_date” is supposed to be a fact which should allow you to use it, according to the syntax, e.g.: 
SELECT MIN({fact/opportunity_amount})
, but it seems that there may be some non-numerical characters in this fact, hence the error. Could you check the column in your database and make sure that it contains only numerical values? If so, please place your LDM in edit mode and refresh the database tables on the left side panel and re-add the dataset to the LDM, this will force any previous missloaded values to have the correct datatype. Please also watch for any breaking changes this may introduce to the workspace, for example, some dashboards and metrics may get broken if the dataset connections or definitions are different.
j
thanks @Moises Morales I guess it may be passed in as a timestamp with timezone coding and perhaps that would count as non-numeric. I will check it out.
11 Views
Open in Slack
PreviousNext
Powered by