Hi i am trying to create a metric of the datetime ...
# gooddata-cloud
c
Hi i am trying to create a metric of the datetime diff between two date measures that can sometimes be null and I keep getting a missing aggregation error, this is my code
select (DATETIME_DIFF({label/delayed.day},{label/fulfilled.day} ))where {label/fulfilled.day} <> NULL and {label/delayed.day} != NULL
k
Thinking completed DATETIME_DIFF Missing Aggregation Error There are two issues with your current formula: 1. Missing Aggregation
DATETIME_DIFF
is not an aggregation function. You must either: â€ĸ Apply aggregation inside
DATETIME_DIFF
(on the date arguments), or â€ĸ Wrap
DATETIME_DIFF
in an aggregation function like
SUM
,
AVG
, etc., using the
BY {dataset}
keyword. Your formula has no aggregation at all, which is why you're getting the error. 2. NULL Filtering Syntax For NULL comparisons in MAQL, you should use
<>
(or
!=
) with
NULL
directly — both are valid per the docs. However, the correct approach for filtering out NULLs on date labels would be:
Copy code
SELECT {label/delayed.day} <> NULL SELECT {label/fulfilled.day} <> NULL
Corrected Formula Assuming
delayed
and
fulfilled
are date dimensions referenced from a common dataset (e.g.,
orders
), use the
BY {dataset}
pattern:
Copy code
SELECT AVG(   SELECT DATETIME_DIFF({label/delayed.day}, {label/fulfilled.day})   BY {dataset/orders}   WHERE {label/fulfilled.day} <> NULL AND {label/delayed.day} <> NULL )
Or, if you want the sum of differences:
Copy code
SELECT SUM(   SELECT DATETIME_DIFF({label/delayed.day}, {label/fulfilled.day})   BY {dataset/orders}   WHERE {label/fulfilled.day} <> NULL AND {label/delayed.day} <> NULL )
> Important: Replace
{dataset/orders}
with the actual identifier of the dataset that contains your date columns. You must specify the source dataset even if there is only one reference to the Date dataset in your model. [DATETIME_DIFF docs] The NULL filtering using
<> NULL
translates to
IS NOT NULL
in SQL, which is the correct semantic for excluding null date rows. [Relational Operators] 👍 0 button 👎 0 button â„šī¸ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.