How to create a measure in LookML based on different views/explorers

Looker Q&A Looker
,
1

I have created two views from a table with some implemented measures. It’s something basic, illustrated as follows:

view : view_A {

dimension: Time_A {....}
dimension: Value_A {...}
dimension: code_B {..}
measure: Total_Value_A {
         type: sum .... sql: {$Value_A}
          }
}

AND 

view : view_B {

dimension: Time_B {....}
dimension: Value_B {...}
dimension: code_B {..}
measure: Total_Value_B {
         type: sum .... sql: {$Value_B}
          }
}

We have two separate explorers based on each view. When we execute the explorers, we get the correct result when using the time dimension for each one of them. Now, it has been requested to implement another metric, which is a combination of the basic ones:

Total_Value_C = Total_Value_A + Total_Value_B

Is it possible to achieve something like this in LookML? What could be the right approach, especially considering that each measure is based on a different time dimension?

2

Hi there!

This should be totally possible. To add together two numeric measures, you’d use a measure of type number.

The LookML might look something like this:

measure: Total_Value_C {
  type: number
  sql: ${Total_Value_A} + ${Total_Value_B} ;;
}

At the end of the day, it shouldn’t matter what time dimension each measure is based on. In Looker, you can select any measure and group it with any dimension, so measures aren’t really based on any dimension. Of course, if I’m missing something about your use case, please let me know!

3

Hello @sam8 thanks for you reply. The definition of the metric is clear, no doubt in that. However, the question is, what is the relation that we need to create between the two original views? Because it should be available for the user to filter the period of time for both metrics. That is where we are struggling with the code.

4

I see. You’ll have to pick one of the views to define Total_Value_C inside.

If you define it in view A, then you can reference Total_Value_B like this:

measure: Total_Value_C {
  type: number
  sql: ${Total_Value_A} + ${view_B.Total_Value_B} ;;
}

Now, this measure will work for any explore where you have joined in both view_A and view_B.

If you want to use view_A in an explore without view_B, then you can use the fields parameter to remove Total_Value_C, so that you don’t get a LookML error when the validator goes to look for the reference to view_B. You can [exclude the field](http://Exclude%20the field that is causing the error from Explores) with LookML like this:

explore: view_Z { # This explore joins view A but not view B
    fields: [ALL_FIELDS*, -view_A.Total_Value_C] # Exclude view_A.Total_Value_C
    join: view_A {
      ...
    }
  }
1 Like
5

Would it be possible to create a derived table, where all 3 of the views are joined, and then create a custom measure inside of that derived table (which you would then join into your model)? That is what I would do!

2 Likes
6

You can create a standalone view for cross-view measures. One advantage of storing cross-view measures in standalone views is that your Explore won’t break if you don’t include both views in your Explore. For example, in @sam8 's latest reply, the Explore would break if view_B is not joined.

This solution is pretty simple and modular:

views/cross_view_measures.view

view: cross_view_measures {
   
   measure: Total_Value_C {
      type: number
      sql: ${view_A.Total_Value_A} + ${view_B.Total_Value_B} ;;
   }

}

Then later in your Explore, you just need to include this view along with views A and B:

explore/view_A.explore.lkml

include: "views/view_A.view"
include: "views/view_B.view"
include: "views/cross_view_measures.view"

explore: View_A {
   
   # JOIN other views
   join: view_B {
      ...
   )

   # JOIN cross view metrics
   join: cross_view_metrics {}

}

Notice how no join condition was needed for cross_view_metrics. You just need to include it.

It’s that simple!

Hope it helps!

1 Like
7

Did anyone actually try this out and get a standalone view to work?

I think I have this suggestion replicated one-to-one, but Total_Value_C refuses top show up in the explore.