Dynamic Groups/Sets, Dynamic Period and Dynamic Measure

(Ashton Du) #1

Hi there,
I have some requests regarding Looker ML and Visualization. Not sure if my requests are doable in Looker now. Pls find details below.

Background: In general, would like to dynamically create groups or sets for locations or users etc. and then create dynamic dates/periods to compare metrics of the two groups in two different periods.

For example:

I want to create a view that will be similar to the content in above picture.

Group 1 and Group 2: user customized groups. Could be locations or users that are in interests.
Period 1 and Period 2: customized date ranges or dates.
Measure 1 and Measure 2: measures defined in LookML. Could be sum, counts, averages or maybe table calculations.

In this case, the goal is to compare two groups’ performances in two different periods. Want to be able to change dates or values in groups.

I have been search around but could not figure out by myself. Also found out Parameters or Filters but not sure how to apply exactly. In Tableau, once I created filters and added dimensions in view, I can create sets or groups based on the result in view such that I can use this set to do other analysis which is very convenient. Is this also doable in Looker?

Could you pls point a direction?
Really appreciate any input here!



(Izzy) #2

Parameters would be better for pre-defined groups, like if you want to specify in the LookML that “Group A” contains location 1 + location 3 + location 5, then the user can select “Group A” as the “Group 1” parameter and “Group B” as the group 2 parameter.

If you want to have it be more dynamic on the frontend, you might want to use templated filters. That way you could have like “Group 1 Location” as a filter and “Group 1 User” as a filter, and then the person using Looker could enter a few locations + users which would roll into the group. That would be a little bit more complex.

You’d have to built out a group dimension on the backend which would have some CASE WHEN logic based on the templated filter input, which there are syntax examples for in the docs: https://docs.looker.com/data-modeling/learning-lookml/templated-filters#making_a_dynamic_derived_table_with_a_templated_filter. If you need more specific examples, I bet we can cook something up here.

For the period/period analysis part, we have a great help center article written: https://help.looker.com/hc/en-us/articles/360001285847-Timeframe-vs-Timeframe-Analysis-Using-Templated-Filters

Do those two components make sense?


(Ashton Du) #3

Hi izzy,
Thanks a lot for your response.

I have checked the templated filter, timeframe vs timeframe analysis and now i understood how Looker works in my case. I will try it out in Looker and see how it goes. My first impression is that if i need to compare many measures in such case, i have to create lots of filtered measures. By doing so, i’m adding to many measures or dimensions in Explore which will potentially make user confused.

Really appreciate your help! Will reach out again if i have any questions!

1 Like

(Izzy) #4

One thing that can help keep the explore clean is adding “group_label” parameters to the measures, so they all show up under one dropdown sub-menu (like a dimension group).

It sounds like your dream situation would be more of a “Pivot” situation, where you just have 1 measure (or users can pick whichever measure they want) but Pivot on “Period” to spread it out. I’m trying to work out a way to do that in my head, since it is a good idea! I’ll think about it some more and circle back if I come up with something.


(Ashton Du) #5

Good morning izzy,
Hope you’re doing well.
I just want to followup regarding our previous conversations. So for the ‘Pivot’ situation, have you got chance to think about it? Any potential solutions?

In addition, I would like to ask for another help.

Let’s say we have a single table that has following:
order id, order date, user id, user registration date, count of records

Say if i choose as one dimension, and then as one measure. I would like to show the with one more condition: users who also registered in the same week of order date week. Is there any way to do so?

Order Date | No. of Order
2019-04-08 | 76 (orders placed by user registered in week of 2019-04-08)
2019-04-01 | 82 (orders placed by user registered in week of 2019-04-01)



(Izzy) #6

Sure, you could do this with a custom filter fairly easily I think! If you’re using a dimension group, and have a week timeframe, you could do something like ${order_date_week} = ${registered_date_week} and that’d do it.


(Izzy) #7

What you could do, given your requirements, would be to have 1 measure called “Measure 1”, and “Measure 2” and so on and so forth, and a set of filters “Define Measure 1” “Define Measure 2” etc.

if you had a parameter

parameter: define_measure_one {
  allowed_value: {
      label: "Count"
      value: count
etc. more values here

and some measures like

measure: count {
type: count

measure: sum_orders {
type: sum
sql: ${orders}


measure: measure_one {
type: number
{% if parameter define_measure_one = "count" %}
{% elsif parameter define_measure_one = "sum" %}
etc etc etc
{% endif %}

and do that for each of your measures. It’s kind of a “brittle” approach, but if you don’t have too many options and you build it well, it would make life easier for your end users.

I kind of feel like using group_labels to bucket measures and then training the users how to use them would be a better approach.


(Ashton Du) #8

This works! Thanks for the help!

1 Like