Filter BEFORE aggregating

Looker Q&A Looker
1

Please share a link if I’ve missed this somewhere… can’t find the answer. Seems like a pretty basic need/question. I have a lot of detail data, and trying to filter BEFORE the aggregation. For instance…

PK1 Socks 29.00

PK2 Socks 28.00

PK3 Socks 35.00

I am trying to aggregate all sock spend where the individual sock spend was under $30. Without having to pull in all the details. Seems like I’m either doing something wrong or perhaps the model I am using is just not set up to allow for this somehow. When I try to use a custom filter or measure, the field I’m trying to use for either isn’t available - ie, not in the list to choose from. What in the world am I missing?

2

Hiya!

To be able to filter easily on item = Socks and spend < 30, both of these fields must be dimensions.

Then if you add them as filters in the UI or create a filtered measure they will be filtering before aggregation, creating a SQL query like

SELECT sum(spend) FROM table WHERE item = "Socks" and spend < 30

My best guess is that the spend is not a dimension in LookML - is it?

1 Like