Hi,
I am playing with the new variables in questions feature. it’s awesome and nice add.
I am trying to make a variable in a custom sql so i can use it with a “Month And Year” filter.
I made this:
[[AND e.fecha = {{month_year}}]]
But it only work with “Single Date” dashboard filter and not “Month And Year”.
Is there any way to do it?
Thanks
For dashboard filters, you should use “field filters”, eg for the example you have:
[[ AND {{timestamp}} ]] and label that timestamp field as a field filter and link it it fetcha
If you copy+paste the entire query, I can be more exact.
Marking month_year above as a datetime means you’ll be using it as a variable that takes a single value of datetime type.
Hi,
Thanks for the reply, i couldnt make it work.
Here is my sql:
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 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.
Hi sameer
I've defined a "Field list" variable in the SQL question. However, when selecting a value in the dashboard, I get this:
So before using variables, the SQL was like this for getting the data for only the current month:
HAVING date_format(create_time, '%Y-%m') = date_format(now(), '%Y-%m')
Now I have replaced it with this for the field list variable:
[[HAVING {{chosen_time}} ]]
Variable setup:
Any suggestions to what I should do differently?
What widget are you using, and can you open up the javascript console and see if there are any errors logged?
Just based on what you’re showing that should work. Also, if you can dig into the server logs and see if the SQL query is being generated correctly, that would be very useful
When you say widget, do you mean which type of visualisation? It is a simple number as in COUNT(*)
Here is an error from the browser console:
I don't think I have access to the server logs unless they can be accessed from inside Metabase?
you can get to the logs via the drop down menu under your initials, see below
by widget I mean the type of filter you added to the dashboard.
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)
