Filter view before join

Hi all!
I have a question regarding a join that I do.

We have a sales table with approx 2M rows. There is one row per store per day with sales data (aggregated). We want to compare sales from a specific date (day of week) with the same day of week last year for the same store.

I join the table with itself using sql_on:

explore: store_sales { from: store_sales join: store_sales_ly { from: store_sales relationship: one_to_one type: left_outer sql_on: ${store_sales.store_id} = ${store_sales_ly.store_id} AND ${store_sales.business_week_of_year} = ${store_sales_ly.business_week_of_year} AND ${store_sales.business_day_of_week_index} = ${store_sales_ly.business_day_of_week_index} AND ${store_sales.business_year}-1 = ${store_sales_ly.business_year} ;;

This works fine. The problem is that it’s joining all 2M and I don’t need that (takes some seconds as well to calculate). The date filter is added as a where-clause and is executed after the join. Unnecessary join to compare two dates - where there are approx 300 rows per date. There must be a better way to do this! Any suggestions?

I would like to filter the store_salesand store_sales_lytable before the join. Can this only be done on a derived table with liquid variables as shown here?: https://docs.looker.com/reference/field-params/filter?version=6.16&lookml=new

Well, any suggestions would be appreciated.

Cheers,
Cris

I think it will be easy when will do it in lookml way. You can just create a view for store_sales table and below is example for current month and last month count.

measure: prior_month_total_query_count {
    type: count
    filters: {field: start_date value: "last month"}
    value_format_name: decimal_0
    alias: [prior_month_total_job_count]
  }

measure: current_mtd_query_count {
    type: count
    filters: {field: start_date value: "this month"}
    alias: [current_mtd_job_count, current_month_job_count]
}

Need to play little bit for weekly. The above filter exactly works like as shown in below screen shot. If you review the sql query for this you will get an idea.

Check the advanced filter help here to know what filters can by used so you can try for last year and current year- https://docs.looker.com/reference/filter-expressions?version=6.14

1 Like