Filter selected on dashboard is Time -> Month and Year.
I found two errors in the Logs that look relevant:
And
I discovered that I am able to make it work on other of my SQL questions. But with this one it will not work.
Here is the full query (I’m new to SQL, might not be pretty):
SELECT COUNT(*) FROM
(SELECT * FROM
(SELECT * FROM payment WHERE state = 'confirmed'
ORDER BY create_time ASC) p
GROUP BY merchant_id
/* HAVING date_format(create_time, '%Y-%m') = date_format(now(), '%Y-%m') */
HAVING {{chosen_time}}
) t2
So a couple of things, we don’t “follow” a column definition from a subselect out to it’s enclosing context. So you should use the {{chosen_time}} in a place where that field could actually be used. In the outer-most Select clause, you don’t have access to that field of the payments table. You have access to the column of that name in the subselect result.
Secondly, I’m not sure exactly what you’re trying to do here. By my read you can just do:
SELECT COUNT (DISTINCT merchant_id)
FROM payment
WHERE state ="confirmed" [[AND {{chosen_time}} ]]
assuming you’re trying to get the number of merchants with a payment for a given date range.
Am I missing something?
I am getting the number of merchants with their first payment in the given date range.
The query works OK when “hardcoding” the time-range into the query. It only doesn’t work when wanting to make it a variable. Is there a difference here between if a field is accessible when hardcoded and when set as a variable?
Also, it works when using this approach and setting the variable as a Date type and selecting Single Date in the dashboard:
HAVING date_format(create_time, '%Y-%m') = date_format({{chosen_time}}, '%Y-%m')
It seems to be only the Field Filter variable type that doesn’t work here.
Huh. I’m not sure I see how that gets the first payment, but that’s irrelevant to the issue at hand.
What we’re doing mechanically with a {{variableName}} clause (in the case where it’s marked as “field filter”), is to replace that tag with the SQL that gets spat out when our underlying query query language for a filter clause is compiled down to a given SQL dialect. More specifically, the query builder (which is not enabled for SQL cards yet) and the dashboard filters generate filter clauses like [=, fieldID, someValue], and we’ll inject `fulllyQualifiedFieldName = someValue" into the SQL template. For this to work, we need the field used in a place where that fully qualified field (eg, SomeTable.SomeField) makes sense.
In your specific example, outside of the innermost SELECT, you don’t have the table fields in scope as you’re selecting from the results of a sub select instead of the actual table.
One alternative, though it’s a bit hokey, is to use int parameters for month and year and tie those to an “Other” filter on the dashboard.
Okay, I think I understand.
Is there then any way to use the Month and Year filter on a query like mine? Or Month and Year can just only be used in SQL queries where the variable is in the innermost SELECT?
Hi sameer.
I still can’t make my question work.
I copy again the sql to check if you see something wrong:
SELECT referencia, SUM(consultas) AS consultas
FROM estadisticas e
LEFT JOIN propiedades p ON e.id_propiedad = p.id
LEFT JOIN contratos c ON c.id_propiedad = p.id
LEFT JOIN propiedades_estados pe ON p.id_estado_propiedad = pe.id
WHERE (precio_anuncio = descuento_anuncio OR (precio_anuncio IS NULL AND descuento_anuncio IS NULL))
[[AND {{timestamp}}]]
AND pe.estado IN (‘activo’)
AND c.cerrado = 0
GROUP BY p.id
ORDER BY consultas DESC
I made timestamp a field filter with the field “Fecha” from the table “estadisticas” wich is a datetime column.
I want to be able to filter month and year, but when i add the filter in the dashboard i get the message not valid fields in this question.
@cagiraudo I think there is a subtle bug you’re encountering which is that the query snippet generator doesn’t know to prefix Fecha field estadisticas with e (the table alias).
Can you try removing the e and seeing if that works?
At the moment, it will only work for situations where the filter is in the inner most select. Opened a tracking issue to remind us to try to figure out a way around this in the next revision of the template language https://github.com/metabase/metabase/issues/3243
Hi sameer
I tried to restructure my query to accommodate to this limitation:
SELECT COUNT(*) FROM
(SELECT MIN(create_time) AS first_time FROM payment WHERE state = 'confirmed'
GROUP BY merchant_id
HAVING {{chosen_time}}
) t1It seems to me that {{chosen_time}} is now working within the innermost SELECT. Yet I get the same result in the dashboard and in the server logs.
what SQL is being generated for that query? (it should be in the server logs)
This is the only query in the logs I could find that resembled my own query. Does this tell you something?
what happens if you run the second query?
I get a “column not found error” for payment.create_time
I’ve played around a bit more and it seems I can’t use the field filter in a HAVING clause unless I use that column explicitly in the SELECT. For instance, SELECT MIN(create_time) will not work - it has to be SELECT create_time
Huh.
I think we’re officially outside of my sphere of competence re: SQL details/quirks =)
Hi Sameer, what datatype do i need to use for this?
I’ve tried date and it won’t allow me to add it? (Running 25.2)
Hi,
I’m new to Metabase but I think that you should write as follows:
select
bla bla bla
where {{month_year}}=true
And set the {{month_year}} to field filter and select the timestamp of the table you want.
I hope I understood the problem correctly and it help
@Yakir_David No, that’s incorrect. Field Filters should not include anything, since Metabase generate that part of the query.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
And this topic is 3 years old, so it would probably be best to create a new topic instead.