Line Items

view: line_items {
  label: "Order Items"
  sql_table_name: store_line_items ;;

  dimension: id {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.id ;;
  }

  dimension: currency {
    sql: ${TABLE}.currency ;;
  }

  dimension: quantity {
    type: int
    sql: ${TABLE}.quantity ;;
  }

  dimension: currency_symbol {
    hidden: yes
    sql: CASE
  WHEN <% parameter orders.currency_selection %> = 'USD' THEN '$'
  WHEN <% parameter orders.currency_selection %> = 'GBP' THEN '£'
END ;;
  }

  dimension: total_price_usd {
    type: number
    hidden: yes
    sql: case when ${currency}='GBP' then ${price}*1.53077 else ${price} end ;;
  }

  dimension: total_price_gbp {
    type: number
    hidden: yes
    sql: case when ${currency}='USD' then ${price}/1.53077 else ${price} end ;;
  }

  dimension: additional_tax_total {
    type: number
    hidden: yes
    sql: ${TABLE}.additional_tax_total ;;
  }

  dimension: adjustment_total {
    type: number
    hidden: yes
    sql: ${TABLE}.adjustment_total ;;
  }

  dimension: included_tax_total {
    type: number
    hidden: yes
    sql: ${TABLE}.included_tax_total ;;
  }

  dimension: order_id {
    type: int
    hidden: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: pre_tax_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.pre_tax_amount ;;
  }

  dimension: price {
    type: number
    hidden: yes
    sql: ${TABLE}.price ;;
  }

  dimension: promo_total {
    type: number
    hidden: yes
    sql: ${TABLE}.promo_total ;;
  }

  dimension: tax_category_id {
    type: int
    hidden: yes
    sql: ${TABLE}.tax_category_id ;;
  }

  dimension: variant_id {
    type: int
    hidden: yes
    sql: ${TABLE}.variant_id ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month, year]
    sql: ${TABLE}.created_at ;;
  }

  dimension_group: updated {
    type: time
    timeframes: [time, date, week, month, year]
    sql: ${TABLE}.updated_at ;;
  }

  measure: item_price_only_usd {
    type: sum
    value_format: "$#,###"
    filters: [currency: "USD"]
    drill_fields: [detail]
    sql: ${price} ;;
  }

  measure: item_price_only_gbp {
    type: sum
    value_format: "\"£\"#,###"
    filters: [currency: "GBP"]
    drill_fields: [detail]
    sql: ${price} ;;
  }

  measure: item_price_currency {
    type: sum
    drill_fields: [detail]
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${total_price_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${total_price_usd}
end ;;
  }

  measure: average_item_price_currency {
    type: avg
    drill_fields: [detail]
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${total_price_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${total_price_usd}
end ;;
  }

  measure: total_cost_price {
    type: sum
    hidden: yes
    sql: ${TABLE}.cost_price ;;
  }

  measure: cost_price_only_usd {
    type: sum
    value_format: "$#,###"
    filters: [currency: "USD"]
    drill_fields: [detail]
    sql: ${total_cost_price} ;;
  }

  measure: cost_price_only_gbp {
    type: sum
    value_format: "\"£\"#,###"
    filters: [currency: "GBP"]
    drill_fields: [detail]
    sql: ${total_cost_price} ;;
  }

  measure: cost_price_currency {
    type: sum
    drill_fields: [detail]
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${cost_price_only_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${cost_price_only_usd}
end ;;
  }

  measure: quantity_count {
    type: sum
    sql: ${quantity} ;;
  }

  measure: items_per_order {
    type: number
    value_format: "#,##0.00"
    sql: ${quantity_count}::float/${orders.count} ;;
  }
}

- view: line_items
  label: Order Items
  sql_table_name: store_line_items
  fields:
  - dimension: id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: currency
    sql: ${TABLE}.currency
  - dimension: quantity
    type: int
    sql: ${TABLE}.quantity
  - dimension: currency_symbol
    hidden: true
    sql: CASE
  WHEN <% parameter orders.currency_selection %> = 'USD' THEN '$'
  WHEN <% parameter orders.currency_selection %> = 'GBP' THEN '£'
END
  - dimension: total_price_usd
    type: number
    hidden: true
    sql: case when ${currency}='GBP' then ${price}*1.53077 else ${price} end
  - dimension: total_price_gbp
    type: number
    hidden: true
    sql: case when ${currency}='USD' then ${price}/1.53077 else ${price} end
  - dimension: additional_tax_total
    type: number
    hidden: true
    sql: ${TABLE}.additional_tax_total
  - dimension: adjustment_total
    type: number
    hidden: true
    sql: ${TABLE}.adjustment_total
  - dimension: included_tax_total
    type: number
    hidden: true
    sql: ${TABLE}.included_tax_total
  - dimension: order_id
    type: int
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: pre_tax_amount
    type: number
    hidden: true
    sql: ${TABLE}.pre_tax_amount
  - dimension: price
    type: number
    hidden: true
    sql: ${TABLE}.price
  - dimension: promo_total
    type: number
    hidden: true
    sql: ${TABLE}.promo_total
  - dimension: tax_category_id
    type: int
    hidden: true
    sql: ${TABLE}.tax_category_id
  - dimension: variant_id
    type: int
    hidden: true
    sql: ${TABLE}.variant_id
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.created_at
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.updated_at
  - measure: item_price_only_usd
    type: sum
    value_format: '$#,###'
    drill_fields:
    - detail
    filters:
    - currency: USD
    sql: ${price}
  - measure: item_price_only_gbp
    type: sum
    value_format: '"£"#,###'
    drill_fields:
    - detail
    filters:
    - currency: GBP
    sql: ${price}
  - measure: item_price_currency
    type: sum
    drill_fields:
    - detail
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${total_price_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${total_price_usd}
end
  - measure: average_item_price_currency
    type: avg
    drill_fields:
    - detail
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${total_price_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${total_price_usd}
end
  - measure: total_cost_price
    type: sum
    sql: ${TABLE}.cost_price
  - measure: cost_price_only_usd
    type: sum
    value_format: '$#,###'
    drill_fields:
    - detail
    filters:
    - currency: USD
    sql: ${total_cost_price}
  - measure: cost_price_only_gbp
    type: sum
    value_format: '"£"#,###'
    drill_fields:
    - detail
    filters:
    - currency: GBP
    sql: ${total_cost_price}
  - measure: cost_price_currency
    type: sum
    drill_fields:
    - detail
    sql: case
  when <% parameter orders.currency_selection %> = 'GBP' then ${cost_price_only_gbp}
  when <% parameter orders.currency_selection %> = 'USD' then ${cost_price_only_usd}
end
  - measure: quantity_count
    type: sum
    sql: ${quantity}
  - measure: items_per_order
    type: number
    value_format: '#,##0.00'
    sql: ${quantity_count}::float/${orders.count}