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

[Des2907]

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?

[sam8]

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!

[Des2907]

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. 

[sam8]

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 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 {
      ...
    }
  }

[lindenPMP]

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!