Question

How can I create a calculated measure with fields that are located in different tables ?

  • 7 January 2020
  • 5 replies
  • 5126 views

Hello,


I’m trying to create a calculated measure in a payment table, the calculation is very simple, I need to take the payment amount and divide it by the FX rate that is coming from a daily currencies table, both of the tables are joined to the same explore, but It seems that I can’t do this because both of the measures are coming from different tables.

I’m trying to avoid a table calculation, I need to create a measure that will give me the payment amount in USD, I’m also tying to avoid using a derived table.

Any ideas ?!


5 replies

Userlevel 4

Are the two tables joined in your model? If so, you can use ${view_name.dimension_name} to reference the the other table. See https://docs.looker.com/reference/field-params/sql?version=6.24&lookml=new for more discussion.

Thanks a lot Quinn!

Userlevel 1

This is something I would definitely push upstream rather than calculate at query time - after all, the exchange rate for the day when the transaction happened would never change and needs to be only calculated once. So I would have “original currency amount” and “USD Amount” columns in the transaction table.

Userlevel 2

You’ve hit on a basic flaw in the Looker metadata design - the lack of cross entity/transaction calculations at the ‘explore’ level.


Within the context of how Looker works, I would suggest using a derived view.

Can this also be done when the join between two tables has been completed multiple times with aliases
Say I want to use some dimension on users as a part of a calculation on order_items, but I want to specifically use the join I’ve aliased as buyers (${buyers.dimension_name} ) , not sellers. When I’ve tried doing this, I’m seeing that buyers is an unknown view being referenced. However, if I don’t use this alias, then the join I’m looking to make isn’t well defined, right?

Reply