Two series with different filters applied to the same table on the same chart

Looker Q&A Looker
1

Hi,

I’d like to draw two series of sells over time :

  • sales of group of customer (a WHERE)
  • a specific customer sales (another WHERE)

So far the best solution I found is to create a view with a derived_table :

view: union_customer_sales_a_b {
  derived_table: {
    sql:
        SELECT
          "A" AS selection,
          sales_a.date,
          sales_a.sell_price
        FROM
          `customer_sales` AS sales
        WHERE
          {% condition customer_a %} sales_a.customer_id {% endcondition %}
        UNION ALL
        SELECT
          "B" AS selection,
          sales_b.date,
          sales_b.sell_price
        FROM
          `customer_sales` AS sales
        WHERE
          {% condition customer_b %} sales_b.customer_id {% endcondition %};;
  }

  parameter: customer_a {
    type: unquoted
  }

  parameter: customer_b {
    type: unquoted
  }

  dimension_group: date {
    type: time
    timeframes: [
      date,
      week,
      month,
      quarter,
      year
    ]
    datatype: datetime
    sql: ${TABLE}.date ;;
  }

  dimension: sell_price {
    type: number
    sql: ${TABLE}.sell_price ;;
  }

  dimension: selection {
    type: string
    sql: ${TABLE}.selection ;;
  }

  measure: total_sell_price {
    type: sum
    sql: ${sell_price} ;;
  }
}

Then in the explore I use the selection as a pivot.

I was wondering if there would be a better pattern to approach this. My concern here is that I’ll have to maintain the base customer_sales view and the composite view with an UNION above.