Question

Calculation between two measures with different date filter

• 5 replies
• 2849 views

• Member
• 6 replies

Hi all,

I’m not sure if that is something possible to do or not in Looker but I would like to calculate the difference between two measures that are in the same explore with both having a different filter date. I’m having a hard time getting both numbers filtered independently from one to another in the same explore so I could then do my table calculation.

See screenshot beow:

Thanks!

5 replies

Userlevel 5

Hey Florian! Neither are perfect but I’ve done this two ways:

1. Use table calculations referencing pivot/standard offsets - downside being you’ll have to create it on each Look

2. If you have standard things you want the difference of (this period/last period, etc), you could make measures with the appropriate filters (like “sales_this_period”) and make a measure for the difference (sales_this_period - sales_last_period). Then, your users just need to define the cohorts they’re comparing through parameters or filters (this is the approach I prefer - the less noodling business users have to do, the better)

Hope this helps! I’ll be back at my office in a week and can elaborate more if you still need more help 🙂

Hi Bens,

Thanks for the response!

I have tried the option two but the inconvenient is that it only filter for a specific time frame for each measure. In other words it’s not dynamic and the end user can’t look at next month or last month.

Would love if you could elaborate when you have some time…

Thanks,

Florian

Userlevel 3

Hi @Florian_Tourneur,

Do I understand by ‘filtered independently’ that you want to calculate the difference between sfdc_prior_opportunity.contract_end_date_date and sfdc_revenue.close_date where AT LEAST ONE of the two is in the past 7 days, but not necessarily both?

In that case, I would say you should delete both the filters as shown in your screenshot, hit the Custom Filter checkbox and add:

``````# prior_opportunity.contract_end_date_date in the past 7 days
# OR revenue.close_date in the past 7 days
AND \${sfdc_prior_opportunity.contract_end_date_raw} <= trunc_days(now())
OR
AND \${sfdc_revenue.close_raw} <= trunc_days(now())
``````

With the table calculation (not sure which of yours would be the start and end date):

``diff_days(\${sfdc_revenue.close_date}, \${sfdc_prior_opportunity.contract_end_date_date})``

Hi Dan,

Thanks for the help here! I actually figured it out using this method here..

Userlevel 7

Hi Florian,

Glad you were able to leverage the LookML-based filtered measures for your use case.

For even more flexibility and giving your users more power, I really recommend using custom fields, and I find that sometimes it is hard to discover that they exist, so I wanted to point you to them here: https://docs.looker.com/admin-options/tutorials/managing-bizusers/bizuser-features#enabling_custom_fields