Working with calendar table in multiple joins

[renmellofrey]

Hello!

I am new to the Looker World and the LookML as well.

Recently i have been working in a task that is joining a few tables and its measures in the same date reference.

Being more specific:

I have three tables and each one has a measure that is referenced by its specific date date from the same table. 

For example: 

I have a table called OPA. It has a measure called OPA Ofenders, and when people view it they use a date field called created sorting record. It also has a package ID

I Also have table called NOT on Time. It has a measure called expected outcomes and when people view it they use a date field called deadline date. It also has a package ID

I also have a table called base_package. I has the package ID as its primary key and  a geographic field called destination regional because we check the OPA and the Not on Time results also by Regional.

And i have a calendar dimension table. 

I am trying to join the calendar table with the OPA table and the NOT on Time table. To have their measures referenced by the date from the calendar table.

So i could know the results for both measures in one specific day without the need to first check the result for OPA using the created sorting record and then go to another explore and check the result for the NOT on Time using the deadline date.

This is what i’ve been doing in LookML:

explore: performance_metrics {
  view_name: base_package
  description: ""
  label: "Performance Metrics"


  join: not_on_time {
    view_label: "NOT"
    type: left_outer
    relationship: many_to_one
    sql_on: ${not_on_time.n3pk_package_id} = ${base_package.n1pk_package_id};;
  }

  join: opa_agencia_entregador {
    view_label: "OPA"
    type: left_outer
    relationship: many_to_one
    sql_on: ${opa_agencia_entregador.package_id} = ${base_package.n1pk_package_id};;
  }



  join: dw_dim_calendar {
    view_label: "Calendar"
    type: left_outer
    relationship: many_to_one
    sql_on: ${opa_agencia_entregador.created_sorting_record_date} = ${dw_dim_calendar.date_date} OR ${not_on_time.n3pk_deadline_date} = ${dw_dim_calendar.date_date} ;;
  }

}

So i’ve got the right results for the OPA but it gets me wrong the NOT on Time. Here is a print of the results.

So the first custody transfer date is the created sorting record. And it is equal the calendar date which is the Date Date field. But the deadline Date from the NOt on Time table does not work.

Basically what i want to know is: in this date (from the Calendar date) how are my results for OPA and Not on Time. (they are already calculated from the table measures).

Thanks for the further Help 🙂