fuzin
2020-12-01 19:13:27
Try using:
EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', @DS_END_DATE))
instead of
# EXTRACT(YEAR FROM CAST(@DS_END_DATE AS DATE))
So, I have been trying to set parameters @DS_START_DATE and @DS_END_DATE to filter some data in Data Studio. The idea is to compare the year of a transaction with the year of the date selected by the parameter and create a column that saves the value of another one by using a CASE WHEN clause. The query extract goes as follows:
CASE
WHEN EXTRACT(YEAR FROM fechaTransaccion) = EXTRACT(YEAR FROM CAST(@DS_END_DATE AS DATE))
THEN VlrBruto ELSE 0 END AS VlrBruto_Actual,
CASE WHEN EXTRACT(YEAR FROM fechaTransaccion) = EXTRACT(YEAR FROM CAST(@DS_START_DATE AS DATE))
THEN VlrBruto ELSE 0 END AS VlrBruto_Anterior
I have tried adding a date filter on my data studio report and have already activated date parameters while doing the personalized consult and still is not working. When running the query in bigquery there's a box that says "Undeclared Query Parameters". While running in data studio the prompt says "unexpected consult error".
Any ideas how to solve it? Thanks in advance
Try using:
EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', @DS_END_DATE))
instead of
# EXTRACT(YEAR FROM CAST(@DS_END_DATE AS DATE))
Thanks! This was actually useful and is working when I connect the query to data studio through a personalized consult. However, when I switch to a view in big query it does not work. Any ideas on that?
"@DS_END_DATE", "@DS_START_DATE" are Google Datastudio specific Use standard sql in BigQuery EXTRACT(YEAR FROM fechaTransaccion) = 2020. You can not use this paramters when creating views in BigQuery.