Another question: Can I import my own SQL functions to GoodData hosted? If not, what alternatives do I have? Possible examples: • Age of a person (so DATE_DIFF in year, with 0.00 level of precision) • Union or intersection of intervals of time (example lib that does that in JS: https://moment.github.io/luxon/api-docs/index.html#interval)

You currently can not import our own SQL functions into GoodData MAQL. So you would either need to pre-calcultate the calculations before loading it to GoodData or find a way how to achieve them usign the existing MAQL functions. For the Age of a person, this might be reasonably close to what you want:

SELECT
  (THIS - Date (Birthdate))/365 
  WHERE Date (Birthdate)>(empty value)
  + 0*(COUNT(personID))

• THIS - Date (Birthdate) would be number of days since the birthdate • divided by 365 would return this value in years with decimals • the WHERE condition is there to exclude any instance where we would have empty birthdate • and the 0*COUNT … is a trick to make sure we only get data for dates which really exist in the person dataset and not the whole date dimension. (but since we are multiplying it by 0 it will in no way affect the result

Hi @Michal Hauzírek - thanks for this. I needed to do an age calc today for asset, so I used your example. I found that this formula seemed to work for me:

SELECT
  (THIS - Date (AcquisitionDate)/365
      + 0 * COUNT(AssetId)

But when I add the where clause:

SELECT
  (THIS - Date (AcquisitionDate)/365
  WHERE Date (AcquisitionDate) <> (empty value)
      + 0 * COUNT(AssetId)

I get an error:

Unexpected 'e'. Expecting: SELECT, RANKFUNCNAME, ZERO, FUNCNAME1, FUNCNAMEN, FUNCNAME2, TEXT_ELEM, OBJECT, FUNCNAME12, COUNT, SHORT_STRING, REPORT, PREVIOUS, PERCENTILE, IDENTIFIER, IFNULL, CASE, NEXT, NATURAL, STRING, IF, RUNFUNCNAME, (, REAL, ELEM_IDENTIFIER, -, THIS

Any idea what's causing this? Thanks!

Hi @Mal Sharkey, it complains about the letter “e” so that brings me to suspicion that maybe you put the “(empty value)” there as a string? You would need to select this value from the “Attribute Values” list in the metric editor, not enter it manually as text. You will know you have it right when the value is orange in the metric editor, not black.

Ah - got it. Thanks!

@Michal Hauzírek Thanks for your answer. That COUNT trick is rather peculiar, but your explanation is great 🙂