I have a base table with me on which I am applying the aggregation over the aggregation. How can I make it dynamic in looker.
When I select country dimension in explore, I want to get following sql generated:
select country,count(distinct product_key)
from
(
select country,product_key,max(fsc_status) as fsc_status from
base_table
group by country,product_key
having max(fsc_status)<=2. --this will be a filter selected from dashboard filters
)
When I select category dimension in explore, I want to get following sql generated:
select category,count(distinct product_key)
from
(
select category,product_key,max(fsc_status) as fsc_status from
base_table where {filters}
group by category,product_key
having max(fsc_status)<=2 --this will be a filter selected from dashboard filters
)