Hi everyone,
I’m working on a use case where we need to calculate the average number of sales locations. To do this, we first need to build a CTE (or derived table) that calculates the distinct number of locations based on user-selected aggregation levels — such as retail week, product, stock region, and more. The list of potential aggregation fields is quite long.
After exploring various approaches, the common solution seems to be building a derived table and then joining it back to the explore. However, this approach falls short because we cannot dynamically group by fields selected by users at runtime, which leads to incorrect outputs if additional aggregation fields are selected.
We’re aware of the _is_selected Liquid variable, but maintaining logic for every possible field quickly becomes unmanageable due to the number of options. We were hoping to leverage bind_all_filters to capture runtime filters and dynamically drive the logic — but it doesn’t help with grouping fields in the SQL.
Ask:
-
Is there any way in Looker to dynamically group by the fields in a derived table based on the fields selected by the user in the explore?
-
Is Looker planning to support functionality similar to bind_all_filters or _is_selected that can also apply to grouping logic?
-
Has anyone implemented a scalable pattern for this kind of dynamic aggregation within Looker?
Any insights or workarounds would be greatly appreciated!
Hello fascelfernandes,
Some of us here have run into cases like this before, and it is indeed challenging.
I believe Looker Product team is aware and has considered this type of use case before, but here is currently no item on the public roadmap that would facilitate dynamic-grain derived tables as you described.
People have done this with _in_query statements, as you mention. This is indeed awkward/difficult to maintain, because any additions/deletions of dimensions in underlying explore need to have a corresponding _is_selected statement added/deleted in the derived table.
One idea that may help with this: With API / SDK, it is possible to get the list of all fields in an explore: theGet LookML Model Explore endpoint. Within the response, there will be a set called ALL_FIELDS. You could have a script or process that fetches the latest list of all_fields, then creates a corresponding lookml code that checks _is_selected for every possible field. If you had to fully automate, I imagine you could house this lookml block in a separate lookml file for this specific purpos and automatically update the file, for example with git actions that trigger whenever a branch is committed.
Hi @Kevin_F_McCarth , thanks for the reply.
Unfortunately this solution would not be able to get the average distinct sales locations with stock on the fly since we first need to commit the changes to the branch and then deploy to master and we further use this measure to calculate metrics like the Rate of sales.
