https://www.gooddata.ai/ logo
Join Slack
Powered by
Hi GoodData team, Are we able to utilize the `MEDI...
# gooddata-cloud
m
Hi GoodData team, Are we able to utilize the 
MEDIAN
function in the given use case below? Context: I have here an insight that lists the groups and its assigned users. For each user, the matter count and work done (tasks) metrics are added. While we have the aggregate functions available (e.g., Avg, Median) for each metric column, the requirement is to place the aggregated values as a new column, with each row having the same value (for each user in the group). I've managed to do this for the average part by via custom metrics, and applying a date filter also works as expected. However, I can't seem to get the correct one for the Median values... Is this possible to implement? Happy to send the copy of our LDM (Staging) and metrics via DM, if needed. Thank you so much in advance! 🙇 Metrics for reference:
Copy code
- Matter Count
SELECT COUNT({label/matter_id})
WHERE {label/matter_is_draft} = "false"

- Matter Count By Group
SELECT SUM(SELECT COUNT({label/matter_id}) BY {label/group_name}, {label/user_name} WHERE {label/matter_is_draft} = "false")
BY {label/group_name} ALL OTHER EXCEPT {label/matter_date.day}

- Barrister Count by Group
SELECT {metric/barrister_count} BY {label/group_name} ALL OTHER

- Average Matter Count By Group
SELECT {metric/matter_count_by_group} / {metric/barrister_count_by_group}

- Median Matter Count By Group
SELECT MEDIAN({metric/matter_count}) BY {label/group_name} ALL OTHER

- Tasks (Excl Tax)
SELECT SUM({fact/task_total_fees_excl_gst})
WHERE {label/task_is_draft} = "false"
AND {label/task_is_void} = "false"

- Tasks By Group (Excl Tax)
SELECT {metric/tasks_excl_tax}
BY {label/group_name} ALL OTHER EXCEPT {label/date.day}

- Barrister Count By Group (Task)
SELECT {metric/barrister_count}
BY {label/group_name} ALL OTHER
WHERE {fact/task_total_fees_incl_gst} > 0

- Average Tasks By Group (Excl Tax)
SELECT {metric/tasks_by_group_excl_tax} / {metric/barrister_count_by_group_task}
k
Using MEDIAN as a Column with Per-Row Values Looking at your screenshot and metrics, the issue is clear: your 
Median Matter Count By Group
metric is returning 88 for every user in Team 1, but the expected value should be 9 (the median of the individual Matter Count values: 16, 5, 47, 11, 9, 6, 7). Why the current metric doesn't work as expected Your current metric:
Copy code
SELECT MEDIAN({metric/matter_count}) BY {label/group_name} ALL OTHER
The 
MEDIAN
function here is computing the median across groups (or at the group level), not the median of the individual user matter counts within a group. The 
BY {label/group_name} ALL OTHER
clause locks the aggregation to the group level and overrides all other attributes — so instead of computing the median of each user's matter count within the group, it's likely computing something at a higher grain and repeating it. [MEDIAN function] [BY ALL OTHER] The core challenge What you want is: > "For each row (user), show the median of all users' matter counts within the same group." This requires MAQL to: 1. First compute 
Matter Count
per user (at 
group_name + user_name
grain), and 2. Then take the 
MEDIAN
of those per-user values, grouped only by 
group_name
. The problem is that 
MEDIAN({metric/matter_count})
— when 
matter_count
is itself a 
COUNT
— means MAQL needs to compute the count per user first, then take the median of those counts at the group level. This is a two-pass aggregation (median of a count), which is a known limitation in MAQL. What you can try You could attempt to explicitly pre-aggregate at the user level before applying MEDIAN:
Copy code
SELECT MEDIAN(SELECT COUNT({label/matter_id}) BY {label/group_name}, {label/user_name} WHERE {label/matter_is_draft} = "false") BY {label/group_name} ALL OTHER
This tries to pass the per-user count as the input to 
MEDIAN
, locked to the group level. However, whether this resolves the two-pass aggregation issue depends on how GoodData's engine handles nested metrics inside 
MEDIAN
. Honest assessment The knowledge sources do not explicitly confirm that 
MEDIAN
of a nested per-user aggregation (i.e., median of a 
COUNT
) works correctly in this pattern. Based on a similar community case, GoodData Support has noted that certain multi-level aggregation patterns are not currently supported. [Community case] I'd recommend: 1. Try the nested approach above and see if it returns the correct value (9 for Team 1). 2. If it doesn't, reach out to GoodData Support directly (as you offered, sharing your LDM and metrics via DM would help them diagnose the exact issue), since this is a nuanced engine-level question about whether 
MEDIAN
supports this two-pass aggregation pattern in your specific model. 👍 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.
m
Hi Mira, my apologies for the delay in getting back to you directly here. Firstly, I’d like to recommend you give the nested approach suggested by our AI Assistant a go:
Copy code
SELECT MEDIAN(SELECT COUNT({label/matter_id}) BY {label/group_name}, {label/user_name} WHERE {label/matter_is_draft} = "false") BY {label/group_name} ALL OTHER
If that doesn’t return the correct value, try this version which also adds 
EXCEPT {label/matter_date.day}
mirroring what you’re already doing in your 
Matter Count By Group
metric:
Copy code
SELECT MEDIAN(
  SELECT COUNT({label/matter_id}) BY {label/group_name}, {label/user_name}
  WHERE {label/matter_is_draft} = "false"
) BY {label/group_name} ALL OTHER EXCEPT {label/matter_date.day}
And for Tasks:
Copy code
SELECT MEDIAN(
  SELECT SUM({fact/task_total_fees_excl_gst}) BY {label/group_name}, {label/user_name}
  WHERE {label/task_is_draft} = "false" AND {label/task_is_void} = "false"
) BY {label/group_name} ALL OTHER EXCEPT {label/date.day}
Let us know if Team 1 comes back as 9 — and if not, please let us know and we’ll take a closer look! 🙂
m
Thanks for stepping in, @Michael Ullock 🙂 Both approaches seemed to have caused a visualization error:
Copy code
java.sql.SQLSyntaxErrorException: (conn=7263305) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY CASE WHEN `def_m_65b6ca22995a1033df0b56cac2474416` THEN `m_65b6ca22995' at line 28
May I know why this is happening?
👀 1
m
Hi Mira, thank you for the error details and your patience while I reviewed this. I’ve been able to trace the exact failure and the root cause. The error you’re seeing: 
You have an error in your SQL syntax... near '(ORDER BY CASE WHEN...
is a known limitation: the 
MEDIAN
function is not supported on MySQL data sources in GoodData Cloud. MySQL does not natively support MEDIAN/PERCENTILE aggregations, and GoodData’s SQL generator produces syntax that MySQL cannot parse when MEDIAN is used. This affects both simple 
MEDIAN
on a direct measure and nested variants — so unfortunately there is no MAQL-only workaround for this. Your options in this case: 1. Use AVG as an approximation — if the distribution of matter counts per user is reasonably symmetric, average may be close enough:
Copy code
SELECT AVG(
     SELECT COUNT({label/matter_id}) BY {label/group_name}, {label/user_name}
     WHERE {label/matter_is_draft} = "false"
   ) BY {label/group_name} ALL OTHER
2. Pre-aggregate in your MySQL database — create a view or summary table that pre-computes the per-user matter count per group. Load that as a separate dataset in GoodData, then apply a simple 
MEDIAN({fact/pre_aggregated_count})
— no nesting, which avoids the unsupported syntax. 3. Switch data source — if you have access to a database that supports MEDIAN natively (e.g., Snowflake, BigQuery, PostgreSQL, or MariaDB), that would resolve the issue entirely. My apologies that I cannot provide you with a better solution at this time, but I hope this helps!
📝 1
m
Hi @Michael Ullock, My apologies for the delayed response. Thank you for finding out the root cause! 🙌 Regarding the options mentioned: 1. I have tried using this metric, however, it doesn't seem to produce the accurate/expected values (see screenshot below ⬇️) 2. While this could be possible, may I confirm if this'll work even with date filters/granularity applied? (please check screenshot as to how the filter is applied) 3. I'm afraid we may not be able to switch to a different data source at this time Apologies for the back and forths here 🙏
m
Hi Mira, to answer your question on option 2, yes, pre-aggregating the median in MySQL should work with date filters, as long as the view includes a date column at the granularity you need (e.g. month). GoodData will then filter the pre-aggregated rows by date just like any other dataset. The idea is to produce one row per group per period with the pre-computed median value, load that as a dataset in GoodData, and reference it as a plain fact, then no 
MEDIAN
function needed, which avoids the MySQL limitation entirely.
👀 1
3 Views
Open in Slack
PreviousNext
Powered by