My query is :-
select count(*) from persons where is_scraped=true and archive=false and is_poi = true and MOD((timezone('Asia/Kolkata', {{created_at}})::date - timezone('Asia/Kolkata',created_at)::date), 14) = 0 and timezone('Asia/Kolkata',created_at)::date != timezone('Asia/Kolkata', {{created_at}})::date
When I used variable date filter , it is working fine.
but I used field filter , it will show this error
Hi @varnika
You cannot use Field Filters like that. Metabase inserts SQL in place of the variable. If you use the simple Date filter, then the variable is just replaced with the date.
https://www.metabase.com/learn/sql-questions/field-filters
I didn't get you.
Actually I want last 7 day data in the same query given above. I want use relative date field filter here.
I have gone through that documentation many times but didn't understand why field can not work here.
@varnika You don't control the SQL generated from Field Filters - Metabase does.
When you do something like where {{fieldfilter}}, then Metabase will convert it to example:
where table.column >= '2021-01-01' and table.column < '2021-08-01'
or
where table.column in ('value1', 'value2')
Here is my another query :-
select op.product_id, p.upc, cd.name as category, p.model as name, sum(op.quantity) as qty, round(sum(op.total*(o.membership_discount)/100),4) as membership_discount, round(sum(op.total)) as total, round(sum(op.tax*op.quantity), 4) as tax, round((sum(op.quantity)*max(pwb.weight_base))/1000,3) as weight
from
oc_order o
left join oc_order_product op on op.order_id = o.order_id
left join oc_product p on p.product_id = op.product_id
left join oc_product_weight_base pwb on pwb.product_id = op.product_id
left join (select pc.product_id, min(pc.category_id) as category_id from oc_product_to_category pc join oc_category c on c.category_id = pc.category_id where c.parent_id = 0 group by pc.product_id) ptc on op.product_id = ptc.product_id left join oc_category_description cd on ptc.category_id = cd.category_id
where
o.order_status_id in (5,2,3,19)
and o.delivery_date>=date({{start_date}}) and o.delivery_date<=date({{end_date}})
and o.shipping_warehouse_id in ({{snippet: Select warehouse id from active warehouses}} ({{warehouse}}))
group by op.product_id;
Here, if I used filed filter in place of start date and end date. It not worked but if I used filed filter in warehouse , it worked.
@varnika As described in the article - please read the entire article carefully again.
https://www.metabase.com/learn/sql-questions/field-filters#field-filters-are-incompatible-with-aliasing
You cannot use table aliases with Field Filters.
https://www.metabase.com/learn/sql-questions/field-filters#omit-the-direct-assignment-in-the-where-clause
You can only use Field Filter by itself in the where-clause without column, operator or function references.
Thanks for your help Flamber.
I have read all the documentation before using filed filter itself. But the question is aliases used for both variables (date and warehouse) but for warehouse filed filter working fine whereas for start date and end date it gives error.
Can you clarify that?
@varnika I don't understand. You need to post screenshots of the variable sidebar.
The {{warehouse}} works because it does not have anything around it, but you have wrapped your data variables in a function and also using table aliases.
