Filter Dimension based on groups of values in LookML

Looker Q&A Looker
,
1

Hello! :slightly_smiling_face:

I have a source table with a limited set of dimensions = shopify_order, sales_order, invoice, forecast.

I need a filter that allows the user to select either of two combinations:

  • combination 1 = shopify_order, forecast, sales_order (all options except invoice)
  • combination 2 = shopify_order, forecast, invoice (all options except sales_order)

Example data:

shakermakerk_0-1720521864185.png
shakermakerk_0-1720521864185.png654×500 16.2 KB

Filtering directly on the dimension in a dashboard gives something like this, which obviously will lead to problems.

shakermakerk_1-1720522105899.png
shakermakerk_1-1720522105899.png1100×184 6.65 KB

I tried solving the problem using parameters and setting the parameter value as the filter options and passing that to a filter on the explore but it doesnt work, it just passes the Liquid code a a string. Would LOVE ideas on how to fix this?

parameter: transaction_type_selector {
    type: unquoted
    allowed_value: {
      label: "invoice"
      value: "'invoice','shopify_order', 'forecast'"
    }
    allowed_value: {
      label: "sales_order"
      value: "'sales_order','shopify_order', 'forecast'"
    }
  }

Explore

explore: fct_retail_shopify_combined_actuals {
  hidden: no
  label: "6. Shopify & Netsuite (retail) Combined"
  view_label: "Transactions Lines"
  group_label: "Core"
  description: "This table is a combination of the Shopify and NetSuite data. It is used to calculate the actual transaction values for the business overall. - Each row of this table is a grouping of customer, sku, transaction date, and channel.- Shopify data includes all legitimate orders from May 2023, using the customer paid value in USD.- NetSuite data includes non-voided sales order data for known retail customers."
  always_filter: {
    # filters: [transaction_type: "'invoice_sent','shopify_order'"]
    filters: [transaction_type: "{% transaction_type_selector._parameter_value %}"]
    
    

}

}
2

Hi @shakermakerk !

You can accomplish this using a derived table. Something like the below, replacing … with whatever the default where clause should be:

view: fct_retail_shopify_combined_actuals_dt {
  derived_table: {
    sql: SELECT transaction_type, value FROM fct_retail_shopify_combined_actuals
    {% if transaction_type_selector._parameter_value == 'sales_order' %}
    WHERE transaction_type IN ('shopify_order', 'forecast', 'sales_order')
    {% elsif transaction_type_selector._parameter_value == 'invoice' %}
    WHERE transaction_type IN ('shopify_order', 'forecast', 'invoice')
    {% else %}
    ...
    {% endif %} ;;
  }
  parameter: transaction_type_selector {
    type: unquoted
    allowed_value: {
      label: "Sales Order"
      value: "sales_order"
    }
    allowed_value: {
      label: "Invoice"
      value: "invoice"
    }
  }
  dimension: transaction_type {
    type: string
    sql: ${TABLE}.transaction_type ;;
  }
  dimension: value {
    type: number
    sql: ${TABLE}.value ;;
  }
}

Hope this helps! Let me know if you have any questions.

3

Hey Katie! this as a fantastic solution and works perfectly, thank you!