Hello, I'm using metabase 0.33.6 that connects to a SQLite database.
I have a problem with formatting dates as a result of a SQL native query.
I link to a table that contains all the incomes with date, document and single product informations. To get the average incomes per days, I used this nested query:
SELECT date("DATA") AS "DATA", avg("sum") AS "Media"
FROM (
SELECT date("movements"."DATA") AS "DATA", "movements"."DOC_ID" AS "DOC_ID", sum("movements"."Importo") AS "sum"
FROM "movements"
GROUP BY date("movements"."DATA"), "movements"."DOC_ID"
ORDER BY date("movements"."DATA") DESC, "movements"."DOC_ID" ASC
)
GROUP BY date("DATA")
ORDER BY date("DATA") DESC
What I can't get in the result, is the DATE column to be reckokn as a date type, but it's shown jsut as a YYYY-MM-DD text fiels.
If I display just the table, the date is properly formatted. If I run any question (apart for the native SQL ones), the field is properly shown as a date field.
I'm really new to metabase and couldn't find any documentation to look at this two particular topics (field formatting and nested queries).
Hi @giacomo
That’s a limitation of SQLite. Date fields are treated as strings, so Metabase does not know it’s a date column, when using Native question.
You need to return the column already formatted by using strftime
But your query looks fairly simple, so I think you might be able to do the entire question via Notebook editor (Custom question), by first doing the sub-query and then add another Summarize to the aggregated result. https://www.metabase.com/docs/latest/users-guide/custom-questions.html
Hi @flamber, thank you so much.
It’s really easier to use Notebook, I just did not understood the logic of it and couldn’t get it working before a careful reading of the article you pointed out. I will look better at the documentation before asking again a dumb question.
Thanks for the quick and nice help.
Just one last thing: I don’t know if this is the proper behavior or if it is a known bug: whenever I create the new question with the “double summarize”, if I apply/remove a filter the second summarize is actually removed from the question.
Am I doing something wrong?
Thanks in advance.
on the first post