Hi Everyone, I am new to GoodData and MAQL: I am t...
# gd-beginners
m
Hi Everyone, I am new to GoodData and MAQL: I am trying to create a metric from when a course was assigned, to when it was completed. For example it took 5.3 days to complete. Here are my fields, nothing I am doing is working. {label/DATE_MODULE_ASSIGNED.hour} (NOTE: This is when the course was assigned) {label/DATE_SAFE_CERTIFIED.hour} (NOTE: This is when the course was completed) Any help with the MAQL statement?
p
Hey Matt - I assume this could help till someone from GoodData responds 🙂 https://www.gooddata.com/docs/cloud/create-metrics/maql/time-arithmetics/datetime-diff/
m
Thank you! I have been giving it a look, and I am missing something, I am down to 1 error, but its a big one.
Statement I landed on: SELECT DATETIME_DIFF( {label/DATE_SAFE_CERTIFIED.hour}, {label/DATE_MODULE_ASSIGNED.hour}, "DAY") By {dataset/MODULES_ASSIGNED_TO_EMPLOYEES}
Error:
Screenshot 2024-05-30 at 4.13.13 PM.png
p
Maybe try removing hour - i don't think you can have the granularity of a day when you are only comparing hour figures
SELECT DATETIME_DIFF( {label/DATE_SAFE_CERTIFIED}, {label/DATE_MODULE_ASSIGNED}, "DAY") By {dataset/MODULES_ASSIGNED_TO_EMPLOYEES}
m
Progress lol. I used: SELECT DATETIME_DIFF( {label/DATE_SAFE_CERTIFIED.day}, {label/DATE_MODULE_ASSIGNED.day}, DAY) By {dataset/MODULES_ASSIGNED_TO_EMPLOYEES} Got this Error:
Copy code
{
  "title": "Bad Request",
  "status": 400,
  "detail": "A result cache error has occurred during the calculation of the result",
  "resultId": "0806b441e28d3962633fda724a20c817774dac1c",
  "reason": "Missing aggregation, from='[attribute/DATE_MODULE_ASSIGNED.day/41ff8424e5fe42de8627673378d040e1, attribute/DATE_SAFE_CERTIFIED.day/41ff8424e5fe42de8627673378d040e1]' to='[]'",
  "traceId": "b8e60fe2f3b9a813f5dd72d5ea2ebe36"
}
Is it because of possible empty values in the tables,
p
Need a SUM out front 🙂
ğŸŽ–ï¸ 1
m
SUM did IT!!
Huge thank you! stranger.
p
no worries!
🙌 1