Create DATE filter from concatenation of two INT fields

Get Help
1

Hi,
I have a table where year and month are represented as two separate INT fields.

I am trying to concatenate them so that I can use them as a DATE filter in Metabase, but I am probably doing something wrong…

Here is my query; I am using MySQL 5.7 and Metabase 0.33.0
select
c.nome_completo
,sum(fat.valor_emitido) as boleto
from faturamentos fat
inner join clientes c on c.idcliente=fat.idcliente
[[ where
cast(concat(ano_referencia,"-",lpad(mes_referencia,2,“0”),"-01") as date)>={{ ano_mes }} ]]
group by c.nome_completo
order by boleto desc
[[limit {{qtde_desp}}]]

If I “play” the query, I get what I want, but I can link a dashboard filter to {{ ano_mes}} :frowning:

Can anybody give me some ideas?

Thanks in advance,

2

Hi @Pbal
It looks correct. And you’re using Date filter, not Field Filter?
Are you seeing any errors in the Metabase log or browser console?

But try avoiding the spaces in the variable {{ ano_mes }} -> {{ano_mes}}
I’m not sure if [[limit {{qtde_desp}}]] might be breaking something. The filters are only for WHERE parameters, so if used elsewhere, it might not work correctly.

3

Hi @flamber,

Thanks for reply, let me show some screenshots:

16
161302×820 22.3 KB

It seems I cannot post more that one screenshot...

I am sort of lost here; I am thinking of creating a VIEW and check whether it works.

If you can give some ideas, that would be much appreciated!

Thanks again!

4

@Pbal
Yes, in your case, I would recommend that you create a view, so Metabase just sees a date field - that would also mean that you can use the query browser instead of only using SQL.