https://www.gooddata.ai/ logo
Join Slack
Powered by
Hi is there a way in MQL to get the max date of th...
# gooddata-cloud
p
Hi is there a way in MQL to get the max date of the data set but capped to today? i have the following formula but i dont think its supported:
Copy code
SELECT COUNT({label/employment_dates.employee_id}) 
WHERE {label/date.day} = IF(MAX({label/date.day}) > TODAY(), YESTERDAY(), MAX({label/date.day}))
USING {dataset/employment_dates};
k
You’re right that the expression you wrote is not supported as-is:
Copy code
SELECT COUNT({label/employment_dates.employee_id}) 
WHERE {label/date.day} = IF(MAX({label/date.day}) > TODAY(), YESTERDAY(), MAX({label/date.day}))
USING {dataset/employment_dates}
From the available docs and community answers, there is: • 
MAX(date_attribute[, dataset])
to get the latest date in context. [MAX] • Time macros like 
THIS
, 
PREVIOUS
, 
NEXT
, but no 
TODAY()
/ 
YESTERDAY()
functions, and no examples of comparing 
MAX(date)
to “today” in MAQL. [Time macros] • 
DATETIME_ADD
that can shift dates, including using 
MAX(date, dataset)
as input, but again no way shown to reference “today” as a date value in MAQL (only as relative macros like 
THIS(DAY)
). [DATETIME_ADD] The knowledge sources do not show any supported pattern to: 1. Compare 
MAX(date)
to “today”, and 2. Conditionally cap it at today inside a metric. They only show using 
MAX(date)
directly to filter other metrics, for example:
Copy code
SELECT SUM({fact/snapshot_value}) 
WHERE {attribute/snapshot_date.day} =
      (SELECT MAX({attribute/snapshot_date.day},{dataset/fact_dataset}))
and variants with 
BY ALL OTHER
. [MAX] Given this, I cannot confirm a supported MAQL way to “cap” the max dataset date at today. The documented options are: • Use 
MAX({label/date.day}, {dataset/employment_dates})
directly as your latest date filter (no capping). • Or use time macros like 
THIS(DAY)
/ 
PREVIOUS(DAY)
etc. for “today” / “yesterday”, but not in a conditional comparison with 
MAX(date)
as you attempted. [Time macros] If you must enforce “no later than today”, the docs and community threads suggest that such more complex date logic is often handled in ETL or via precomputed flags, not purely in MAQL. [Slack thread] 👍 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.
s
Hi Pouyan. Please try the recommendation from our AI and let us know. One more recommendation: to use the maximum date for all rows, add 
BY ALL OTHER
to the syntax.
Copy code
SELECT SUM({fact/snapshot_value}) 
WHERE {attribute/snapshot_date.day} =
      (SELECT MAX({attribute/snapshot_date.day},{dataset/fact_dataset}) BY ALL OTHER)
p
my goal is to get the max if its not in the future, if it is get today instead
an expression like this would do but doesnt seem to be supported: IF(MAX({label/date.day}) > TODAY(), TODAY(),
s
Thank you for the details, Pouyan. Looking at your goal — filtering to the lesser of MAX(date) or today — the challenge is that MAQL doesn't support an 
IF()
function syntax or a direct 
TODAY()
function. However, you can achieve this using the 
THIS(DAY)
time macro and MAQL's supported constructs. Recommended Approach: The cleanest supported way is to use `GREATEST`/`LEAST` logic or restructure the WHERE clause. Since MAQL supports 
THIS(DAY)
for "today" and 
MAX
for date attributes, you can cap the date filter using a nested metric with `LEAST`: 
SELECT COUNT({label/employment_dates.employee_id})
WHERE {label/date.day} = (
SELECT LEAST(
MAX({label/date.day}, {dataset/employment_dates}),
THIS(DAY)
)
)
This uses: • 
MAX({label/date.day}, {dataset/employment_dates})
— gets the maximum date in the dataset (the second parameter specifies which dataset to search, required for date attributes) [MAX aggregation] • 
THIS(DAY)
— references today's date [Time Macros] • 
LEAST(...)
— returns the smaller of the two values, effectively capping the max date at today Important Notes 1. 
LEAST
returns a numeric/datetime result — since both arguments are date-type, this should work as a comparison value in the WHERE clause. 2. The 
USING
keyword is an alternative to the second parameter in 
MAX
for date attributes. Either 
MAX({label/date.day}, {dataset/employment_dates})
or 
SELECT MAX({label/date.day}) USING {dataset/employment_dates}
should work. [MAX aggregation] 3. 
IF()
as a function is not supported in MAQL — 
IF
is a statement keyword (
IF ... THEN ... ELSE ... END
), not a callable function, so your original syntax won't work.
p
thanks, trying the recommended option i get the following error:
s
Sorry for that. The problem is that 
LEAST
returns a NUMERIC value, but 
{label/date.day}
is a DATE type, and MAQL does not allow 
DATE = NUMERIC
comparisons. Instead of computing the capped max dynamically, you can filter to dates that are both equal to the max date AND not in the future: 
SELECT COUNT({label/employment_dates.employee_id})
WHERE {label/date.day} = (SELECT MAX({label/date.day}, {dataset/employment_dates}) BY ALL OTHER)
AND {label/date.day} <= THIS(DAY)
This approach: • Finds the max date in the dataset [MAX aggregation] • Adds a cap so that if the max date is in the future, no rows match that future date, and the 
<= THIS(DAY)
condition effectively filters it out [Time Macros] Caveat: If the max date is in the future, this would return 0 (no rows match), rather than falling back to today's count. If you need the fallback to today's data when the max is in the future, that conditional branching logic is not directly expressible in MAQL, as 
IF()
as a function is not supported. Unfortunately, there are no other workarounds for that.
p
yeah i need to fall back to todays date so there is a result
m
Hi Pouyan, I’d just like to confirm Shamils feedback that the conditional fallback logic you need “use the max date in the dataset, but fall back to today if that max date is in the future” is not achievable in a single MAQL metric. MAQL’s 
IF
is a statement that returns numeric results only, 
TODAY()
does not exist (the equivalent is 
THIS(DAY)
), 
LEAST
cannot operate on date types, and the dual-condition approach (
WHERE date = MAX(...) AND date <= THIS(DAY)
) returns 0 rows rather than falling back to today when the max date is in the future.
6 Views
Open in Slack
PreviousNext
Powered by