Percentile in Custom Question

Bug reports
1

Hi there!
I've encountered with one issue in a Custom Question section.
I want to find a percentile for a simple table:
46
And this is how I implemented Custom Question

12
122552×1132 126 KB

For a "prc50" in Summarize field I've used this formula:
= Percentile([value], 0.5)
And, after click on "Visualize" button I see this error:
51
511684×840 70.5 KB

The total query from a Custom Question is:

SELECT "source"."category" AS "category", PERCENTILE_CONT(0.5) within group (order by "source"."id") AS "prc50"
FROM (SELECT * FROM (
    VALUES
        (100, 'a'),
        (100, 'b'),
        (50, 'a'),
        (80, 'a')
) AS t (value, category)) "source"
GROUP BY "source"."category"
ORDER BY "source"."category" ASC

Metabase version - v0.41.5
Browser - Google Chrome 100.0.4896.88, (x86_64)

2

Hi @Mikhail
Not all databases supports all the Custom Expressions:
https://www.metabase.com/docs/latest/users-guide/expressions-list.html#database-limitations
You should open an issue in the driver repo:
https://github.com/dacort/metabase-athena-driver

3

Hi @flamber!
Thanks for your answer!
But I guess that the problem not in driver...because Athena is similar to Presto DB. On the limitations page - "Additionally, Presto only provides approximate results for Median and Percentile ." Looks like the results should be provided, with some exceptions (approx).
And you could see that error message doesn't look like "This function is not registered" . It points out to some syntax error in a query.

Anyway, this is just my thoughts. If you 100% sure that the problem lays in driver - I'll create an issue there...

4

@Mikhail Athena might originally was based on PrestoDB, but a lot has changed since - it's now completely its own database type, and by such its own driver.

I don't know what your Custom Expression is - I cannot see that - but the query says you are referencing the field [id], though you don't have such column in your SQL question - it's called value.

But your problem is not Metabase or any of the officially supported drivers, but with a third-party driver.

1 Like