Question

Filter BEFORE aggregating

  • 5 October 2021
  • 1 reply
  • 12 views

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?


1 reply

Userlevel 1

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? 

 

Reply