Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Excluding data only if dimension is selected (grouped by)

  • 21 April 2017
  • 5 replies
  • 291 views
IanT
Userlevel 7
Badge
  • IanT
  • Good Looker
  • 412 replies

I have a tricky problem which I hope someone has a good idea/hack for.


I have a products table which contains sales by a product name, month and a flag for if this is a top 300 product in that month.


I have a category table which is a mapping table of products to category.


Only top 300 products are ever mapped to a category, once a product is mapped it will stay so even if it drops out of the top 300 in following months.


My user says using the category filter is fine at all times but when including category in a group by we need to limit the product data to the top 300 products - they have their reasons which are not going to be changed - nor is the data.


In summary, we want to use categories for products which may no longer be in the top 300 however when analyzing results by category they want it to be fair.


Any ideas how this can be achieved? - I cannot filter the dimension because we want to populate the full list in the filter likewise for the same reason I cant only join on the top 300.

5 replies

A
Rank

Hey @IanT, how are you calculating top 300 per month? It sounds the best way to accomplish this would be to include that field in the query as well, and then pivot on category, which will give you that month’s categories. Let me know if this helps!

IanT
Userlevel 7
Badge

top 300 is done outside of our systems i think and then the categorization is outsourced. The problem would still remain that the user would have to know it pull the top300 flag into the query if grouping by but filtering is fine.

A
Rank

@IanT In that case – it may be worth looking into creating a second category dimension, this one with the required_fields parameter on – so that top 300 is always pulled into the query. Would look something like this:


dimension: category_for_top_300 {

  sql: ${category} ;;

  required_fields: [top_300_product]

}

This will add the top_300_product dimension to the query without displaying it to the user. You can read up on the required_fields parameter here !

IanT
Userlevel 7
Badge

Hi, yes I read up about this before posting but realised that this wouldn’t make a difference since it would just group on the nulls in top 300 flag and show the category anyway, also i dont want to expose 2 fields (one for filtering one for grouping) as it will be very confusing for the user.

A
Rank

Got it @IanT. In this case, I would recommend that you visit us at help.looker.com to discuss further. We might be able to suggest remodeling the data in some way to accommodate your use case!

Reply


Terms of Service