Hi everyone,
Since we can't pass filter to a pivot in a dashboard, I am trying to find any workaround.
I found that in BigQuery, we can use 'execute immediate' for dynamic pivot without using pivot chart.
The sample of that is below
execute immediate (
select '''select * from (select warehouse_name, product_name, sum(gmv) as gmv fromdgn_principal.fact_transaction_itemwhere {{request_delivery_date}} and nmv > 0 and transaction_status = "delivered" and brand_name = "Oioi" group by 1,2 order by 3 desc)
pivot(sum(gmv) for warehouse_name in ("''' || string_agg(warehouse_name, '", "') || '''"))
'''
from (select warehouse_name fromdgn_principal.fact_transaction_itemgroup by 1 order by warehouse_name)
);
This works like a charm. However, if I set the filter to any value, it returns error.
Do you have any idea?
Thanks!
