Shipping Costs

-- models/shipping_costs.sql
{{ config(materialized='view') }}

select
  o.id as order_line_id, o.orderid as order_id, o.producttype as product_type, o.shipment, o.shipcost as ship_cost, o.status, o.refunded, o.custid as customer_id,
  o.DispMatrixID, o.OrigDelID, o.DispatchStatus, o.DispatchDate,
  trans.date as transaction_date, trans.subaccount, trans.PasRef, trans.StoreID,
  tcurr.description as trans_currency_code, tcurr.id as trans_currency_id,
  t.eventid as event_id,
  md.eventid as merchandise_event_id, md.venueid as merchandise_venue_id, md.tourid as merchandise_tour_id, md.music_groupid as merchandiseandise_description_music_group_id, md.clientid as merchandiseandise_description_client_id, md.promoid as merchandiseandise_description_promo_id,
  tp.platform as platform,
  o.savedate as save_date,
  e.clientid as client_id, e.promoid as promo_id, e.venueid as venue_id
from customer_purchases as o
LEFT JOIN event_entries as t on o.productid = t.id
LEFT JOIN occasions as e on t.eventid = e.id
LEFT JOIN transactions as trans on trans.id = o.transid
LEFT JOIN currency as tcurr on tcurr.id = trans.currencyid
LEFT JOIN currency as lcurr on lcurr.id = o.currencyid
LEFT JOIN merchandise as m on m.id = o.productid
LEFT JOIN merchandiseandise_description as md on md.id = m.descid
LEFT JOIN transactions_platform AS tp ON trans.PlatformID = tp.id
group by o.orderid, o.shipment

# models/schema.yml
version: 2

models:
  - name: shipping_costs
    columns:
      - name: trans_currency_id
      - name: txn_rate_to_usd
        description: "This is the GBP rate to USD"
      - name: order_line_id
      - name: order_id
        tests:
          - unique
          - not_null
      - name: product_type
      - name: pas_ref
      - name: store_id
      - name: ship_type
      - name: shipment
      - name: dispatch_status
      - name: disp_matrix_id
      - name: orig_del_id
      - name: event_id
      - name: artist_id
      - name: client_id
      - name: promo_id
      - name: tour_id
      - name: order_status
      - name: line_status
        description: "This is a concat of the order status & the refunded flag"
      - name: trans_currency_code
      - name: refunded
      - name: subaccount
      - name: platform
      - name: customer_id
      - name: ship_cost
      - name: ship_cost_vat
      - name: ship_cost_less_vat
      - name: ship_cost_gbp
      - name: ship_cost_vat_gbp
      - name: ship_cost_usd
      - name: ship_cost_vat_usd
      - name: refund_type
      - name: ship_cost_refund_amount
      - name: ship_cost_refund_amount_gbp
      - name: ship_cost_refund_amount_usd
      - name: ship_cost_less_refunds_gbp
      - name: ship_cost_less_refunds_usd
      - name: refunded_ship_cost_less_vat
        description: "This is the amount of Ship Cost refunded less VAT"
      - name: refunded_ship_cost_vat

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select
  o.id as order_line_id, o.orderid as order_id, o.producttype as product_type, o.shipment, o.shipcost as ship_cost, o.status, o.refunded, o.custid as customer_id,
  o.DispMatrixID, o.OrigDelID, o.DispatchStatus, o.DispatchDate,
  trans.date as transaction_date, trans.subaccount, trans.PasRef, trans.StoreID,
  tcurr.description as trans_currency_code, tcurr.id as trans_currency_id,
  t.eventid as event_id,
  md.eventid as merchandise_event_id, md.venueid as merchandise_venue_id, md.tourid as merchandise_tour_id, md.music_groupid as merchandiseandise_description_music_group_id, md.clientid as merchandiseandise_description_client_id, md.promoid as merchandiseandise_description_promo_id,
  tp.platform as platform,
  o.savedate as save_date,
  e.clientid as client_id, e.promoid as promo_id, e.venueid as venue_id
from customer_purchases as o
LEFT JOIN event_entries as t on o.productid = t.id
LEFT JOIN occasions as e on t.eventid = e.id
LEFT JOIN transactions as trans on trans.id = o.transid
LEFT JOIN currency as tcurr on tcurr.id = trans.currencyid
LEFT JOIN currency as lcurr on lcurr.id = o.currencyid
LEFT JOIN merchandise as m on m.id = o.productid
LEFT JOIN merchandiseandise_description as md on md.id = m.descid
LEFT JOIN transactions_platform AS tp ON trans.PlatformID = tp.id
group by o.orderid, o.shipment

view: shipping_costs {
  derived_table: {
    sql: select
  o.id as order_line_id, o.orderid as order_id, o.producttype as product_type, o.shipment, o.shipcost as ship_cost, o.status, o.refunded, o.custid as customer_id,
  o.DispMatrixID, o.OrigDelID, o.DispatchStatus, o.DispatchDate,
  trans.date as transaction_date, trans.subaccount, trans.PasRef, trans.StoreID,
  tcurr.description as trans_currency_code, tcurr.id as trans_currency_id,
  t.eventid as event_id,
  md.eventid as merchandise_event_id, md.venueid as merchandise_venue_id, md.tourid as merchandise_tour_id, md.music_groupid as merchandiseandise_description_music_group_id, md.clientid as merchandiseandise_description_client_id, md.promoid as merchandiseandise_description_promo_id,
  tp.platform as platform,
  o.savedate as save_date,
  e.clientid as client_id, e.promoid as promo_id, e.venueid as venue_id
from customer_purchases as o
LEFT JOIN event_entries as t on o.productid = t.id
LEFT JOIN occasions as e on t.eventid = e.id
LEFT JOIN transactions as trans on trans.id = o.transid
LEFT JOIN currency as tcurr on tcurr.id = trans.currencyid
LEFT JOIN currency as lcurr on lcurr.id = o.currencyid
LEFT JOIN merchandise as m on m.id = o.productid
LEFT JOIN merchandiseandise_description as md on md.id = m.descid
LEFT JOIN transactions_platform AS tp ON trans.PlatformID = tp.id
group by o.orderid, o.shipment ;;
  }

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

  dimension: txn_rate_to_usd {
    label: "Rate - GBP to USD"
    description: "This is the GBP rate to USD"
    type: number
    sql: ${currency_gbp_to_usd.rate} ;;
  }

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

  dimension: order_id {
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

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

  dimension: pas_ref {
    sql: ${TABLE}.PasRef ;;
  }

  dimension: store_id {
    type: int
    sql: ${TABLE}.StoreID ;;
  }

  dimension: ship_type {
    label: "Shipment Cost Type"
    sql: IF(${product_type} = 'T', 'Ticket Shipment', 'Merch Shipment') ;;
  }

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

  dimension: dispatch_status {
    sql: ${TABLE}.DispatchStatus ;;
  }

  dimension: disp_matrix_id {
    type: int
    sql: ${TABLE}.DispMatrixID ;;
  }

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

  dimension: event_id {
    type: int
    hidden: yes
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.event_id
  WHEN ${product_type} = 'M' AND ((${TABLE}.save_date >= (date_sub(curdate(), INTERVAL 9 month))) OR (${TABLE}.save_date is null))
    THEN ${order_event_id_fperformances.event_id}
  WHEN ${product_type} = 'M' AND ((${TABLE}.save_date < (date_sub(curdate(), INTERVAL 9 month))) AND (${TABLE}.save_date is not null))
    THEN ${order_event_id_archive_fperformances.event_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandise_event_id IS NOT NULL
    THEN ${TABLE}.merchandise_event_id
  ELSE null
END ;;
  }

  dimension: artist_id {
    type: int
    hidden: yes
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${tours.music_group_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_music_group_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_music_group_id
  ELSE ${tours.music_group_id}
END ;;
  }

  dimension: client_id {
    type: int
    hidden: yes
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.client_id
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_client_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_client_id
  ELSE ${TABLE}.client_id
END ;;
  }

  dimension: promo_id {
    type: int
    hidden: yes
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.promo_id
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_promo_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_promo_id
  ELSE ${TABLE}.promo_id
END ;;
  }

  dimension: tour_id {
    type: int
    hidden: yes
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${occasions.tour_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandise_tour_id IS NOT NULL
    THEN ${TABLE}.merchandise_tour_id
  ELSE ${occasions.tour_id}
END ;;
  }

  dimension: order_status {
    hidden: yes
    sql: ${TABLE}.status ;;
  }

  dimension: line_status {
    description: "This is a concat of the order status & the refunded flag"
    sql: if(${refunded} = "yes",concat(${order_status}," & Refunded Yes"),${order_status}) ;;
  }

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

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

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

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

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

  dimension: ship_cost {
    label: "Ship Cost (Trans)"
    type: number
    sql: ${TABLE}.ship_cost ;;
  }

  dimension: ship_cost_vat {
    label: "Ship Cost VAT (Trans)"
    type: number
    hidden: yes
    sql: ${ship_cost} * ${vat_factor_customer} ;;
  }

  dimension: ship_cost_less_vat {
    type: number
    hidden: yes
    sql: ${ship_cost} - ${ship_cost_vat} ;;
  }

  dimension: ship_cost_gbp {
    label: "Ship Cost (GBP)"
    type: number
    sql: ${ship_cost} / ${txn_rate_to_gbp} ;;
  }

  dimension: ship_cost_vat_gbp {
    label: "Ship Cost VAT (GBP)"
    type: number
    hidden: yes
    sql: ${ship_cost_gbp} * ${vat_factor_customer} ;;
  }

  dimension: ship_cost_usd {
    label: "Ship Cost (USD)"
    type: number
    sql: (
  IF(${trans_currency_code} = 'USD', ${ship_cost},
    IF(${currency_gbp_to_usd.rate} IS NULL, ${ship_cost_gbp} * 1.60,
    1.0 * ${ship_cost_gbp} * ${currency_gbp_to_usd.rate}))
) ;;
  }

  dimension: ship_cost_vat_usd {
    label: "Ship Cost VAT (USD)"
    type: number
    hidden: yes
    sql: ${ship_cost_usd} * ${vat_factor_customer} ;;
  }

  dimension: refund_type {
    sql: CASE
  WHEN ${ship_cost} = ${ship_cost_refund_amount} THEN "Full Shipping Refund"
  WHEN ${ship_cost_refund_amount} = 0 THEN "No Refund"
  WHEN ${ship_cost} = ${ship_cost_refund_amount} IS NULL THEN "No Refund"
  ELSE "Partial Shipping Refund"
END ;;
  }

  dimension: ship_cost_refund_amount {
    type: number
    hidden: yes
    sql: 1.0 * IFNULL(((${order_refund_fperformances.shipment_refund_amount})),0) ;;
  }

  dimension: ship_cost_refund_amount_gbp {
    type: number
    hidden: yes
    sql: 1.0 * IFNULL(((${order_refund_fperformances.shipment_refund_amount}) / ${txn_rate_to_gbp}),0) ;;
  }

  dimension: ship_cost_refund_amount_usd {
    type: number
    hidden: yes
    sql: IF(${order_refund_fperformances.currency_id} = 3, (${order_refund_fperformances.shipment_refund_amount}) ,${ship_cost_refund_amount_gbp} * ${txn_rate_to_usd}) ;;
  }

  dimension: ship_cost_less_refunds_gbp {
    label: "Ship Cost Less Refunds (GBP)"
    type: number
    hidden: yes
    sql: ${ship_cost_gbp} - ${ship_cost_refund_amount_gbp} ;;
  }

  dimension: ship_cost_less_refunds_usd {
    label: "Ship Cost Less Refunds (USD)"
    type: number
    hidden: yes
    value_format: "$#,##0.00"
    sql: ${ship_cost_usd} - ${ship_cost_refund_amount_usd} ;;
  }

  dimension: refunded_ship_cost_less_vat {
    label: "Refunded: Ship Cost Less VAT (List)"
    description: "This is the amount of Ship Cost refunded less VAT"
    type: number
    sql: if(${ship_cost} = ${ship_cost_refund_amount}, ${ship_cost_less_vat}, 0) ;;
  }

  dimension: refunded_ship_cost_vat {
    label: "Refunded: Ship Cost VAT (List)"
    type: number
    sql: if(${ship_cost} = ${ship_cost_refund_amount}, ${ship_cost_vat}, 0) ;;
  }

  dimension_group: dispatch {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DispatchDate ;;
  }

  dimension_group: transaction {
    type: time
    timeframes: [time, date, week, month, year, month_num]
    sql: ${TABLE}.transaction_date ;;
  }

  measure: order_count {
    label: "Unique Order Count"
    type: count_distinct
    sql: ${order_id} ;;
  }

  measure: count {
    type: count
    hidden: yes
    drill_fields: [detail*]
  }

  measure: total_ship_cost {
    label: "Total Ship Cost (Trans)"
    type: sum
    sql: ${ship_cost} ;;
  }

  measure: total_ship_cost_vat {
    label: "Total Ship Cost VAT (Trans)"
    type: sum
    sql: ${ship_cost_vat} ;;
  }

  measure: total_ship_cost_less_vat {
    label: "Total Ship Cost Less VAT (Trans)"
    type: sum
    sql: ${ship_cost_less_vat} ;;
  }

  measure: total_ship_cost_gbp {
    label: "Total Ship Cost (GBP)"
    type: sum
    sql: ${ship_cost_gbp} ;;
  }

  measure: avg_ship_cost_gbp {
    label: "Avg Ship Cost (GBP)"
    type: average
    sql: ${ship_cost_gbp} ;;
  }

  measure: total_ship_cost_vat_gbp {
    label: "Total Ship Cost VAT (GBP)"
    type: sum
    sql: ${ship_cost_vat_gbp} ;;
  }

  measure: total_ship_cost_usd {
    label: "Total Ship Cost (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${ship_cost_usd} ;;
  }

  measure: avg_ship_cost_usd {
    label: "Avg Ship Cost (USD)"
    type: average
    value_format: "$#,##0.00"
    sql: ${ship_cost_usd} ;;
  }

  measure: total_ship_cost_vat_usd {
    label: "Total Ship Cost VAT (USD)"
    type: sum
    sql: ${ship_cost_vat_usd} ;;
  }

  measure: total_ship_cost_refund_amount {
    label: "Total Ship Cost Refunded (Trans)"
    type: sum
    sql: ${ship_cost_refund_amount} ;;
  }

  measure: total_ship_cost_refund_amount_gbp {
    label: "Total Ship Cost Refunded (GBP)"
    type: sum
    sql: ${ship_cost_refund_amount_gbp} ;;
  }

  measure: total_ship_cost_refund_amount_usd {
    label: "Total Ship Cost Refunded (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${ship_cost_refund_amount_usd} ;;
  }

  measure: total_ship_cost_less_refunds_gbp {
    label: "Total Ship Cost Less Refunds (GBP)"
    type: sum
    sql: ${ship_cost_less_refunds_gbp} ;;
  }

  measure: total_ship_cost_less_refunds_usd {
    label: "Total Ship Cost Less Refunds (USD)"
    type: sum
    value_format: "$#,##0.00"
    sql: ${ship_cost_less_refunds_usd} ;;
  }

  measure: total_refunded_ship_cost_less_vat {
    label: "Refunded: Total Ship Cost Less VAT (List)"
    description: "This is the amount of Ship Cost refunded less VAT"
    type: sum
    sql: ${refunded_ship_cost_less_vat} ;;
  }

  measure: total_refunded_ship_cost_vat {
    label: "Refunded: Total Ship Cost VAT (List)"
    type: sum
    sql: ${refunded_ship_cost_vat} ;;
  }
}

- view: shipping_costs
  derived_table:
    sql: select
  o.id as order_line_id, o.orderid as order_id, o.producttype as product_type, o.shipment, o.shipcost as ship_cost, o.status, o.refunded, o.custid as customer_id,
  o.DispMatrixID, o.OrigDelID, o.DispatchStatus, o.DispatchDate,
  trans.date as transaction_date, trans.subaccount, trans.PasRef, trans.StoreID,
  tcurr.description as trans_currency_code, tcurr.id as trans_currency_id,
  t.eventid as event_id,
  md.eventid as merchandise_event_id, md.venueid as merchandise_venue_id, md.tourid as merchandise_tour_id, md.music_groupid as merchandiseandise_description_music_group_id, md.clientid as merchandiseandise_description_client_id, md.promoid as merchandiseandise_description_promo_id,
  tp.platform as platform,
  o.savedate as save_date,
  e.clientid as client_id, e.promoid as promo_id, e.venueid as venue_id
from customer_purchases as o
LEFT JOIN event_entries as t on o.productid = t.id
LEFT JOIN occasions as e on t.eventid = e.id
LEFT JOIN transactions as trans on trans.id = o.transid
LEFT JOIN currency as tcurr on tcurr.id = trans.currencyid
LEFT JOIN currency as lcurr on lcurr.id = o.currencyid
LEFT JOIN merchandise as m on m.id = o.productid
LEFT JOIN merchandiseandise_description as md on md.id = m.descid
LEFT JOIN transactions_platform AS tp ON trans.PlatformID = tp.id
group by o.orderid, o.shipment
  fields:
  - dimension: trans_currency_id
    sql: ${TABLE}.trans_currency_id
  - dimension: txn_rate_to_usd
    type: number
    label: Rate - GBP to USD
    description: This is the GBP rate to USD
    sql: ${currency_gbp_to_usd.rate}
  - dimension: order_line_id
    sql: ${TABLE}.order_line_id
  - dimension: order_id
    primary_key: true
    sql: ${TABLE}.order_id
  - dimension: product_type
    sql: ${TABLE}.product_type
  - dimension: pas_ref
    sql: ${TABLE}.PasRef
  - dimension: store_id
    type: int
    sql: ${TABLE}.StoreID
  - dimension: ship_type
    label: Shipment Cost Type
    sql: IF(${product_type} = 'T', 'Ticket Shipment', 'Merch Shipment')
  - dimension: shipment
    sql: ${TABLE}.shipment
  - dimension: dispatch_status
    sql: ${TABLE}.DispatchStatus
  - dimension: disp_matrix_id
    type: int
    sql: ${TABLE}.DispMatrixID
  - dimension: orig_del_id
    type: int
    hidden: true
    sql: ${TABLE}.OrigDelID
  - dimension: event_id
    type: int
    hidden: true
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.event_id
  WHEN ${product_type} = 'M' AND ((${TABLE}.save_date >= (date_sub(curdate(), INTERVAL 9 month))) OR (${TABLE}.save_date is null))
    THEN ${order_event_id_fperformances.event_id}
  WHEN ${product_type} = 'M' AND ((${TABLE}.save_date < (date_sub(curdate(), INTERVAL 9 month))) AND (${TABLE}.save_date is not null))
    THEN ${order_event_id_archive_fperformances.event_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandise_event_id IS NOT NULL
    THEN ${TABLE}.merchandise_event_id
  ELSE null
END
  - dimension: artist_id
    type: int
    hidden: true
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${tours.music_group_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_music_group_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_music_group_id
  ELSE ${tours.music_group_id}
END
  - dimension: client_id
    type: int
    hidden: true
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.client_id
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_client_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_client_id
  ELSE ${TABLE}.client_id
END
  - dimension: promo_id
    type: int
    hidden: true
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${TABLE}.promo_id
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandiseandise_description_promo_id IS NOT NULL
    THEN ${TABLE}.merchandiseandise_description_promo_id
  ELSE ${TABLE}.promo_id
END
  - dimension: tour_id
    type: int
    hidden: true
    sql: CASE
  WHEN ${product_type} = 'T'
    THEN ${occasions.tour_id}
  WHEN ${product_type} = 'M' AND ${TABLE}.merchandise_tour_id IS NOT NULL
    THEN ${TABLE}.merchandise_tour_id
  ELSE ${occasions.tour_id}
END
  - dimension: order_status
    hidden: true
    sql: ${TABLE}.status
  - dimension: line_status
    description: 'This is a concat of the order status & the refunded flag'
    sql: if(${refunded} = "yes",concat(${order_status}," & Refunded Yes"),${order_status})
  - dimension: trans_currency_code
    sql: ${TABLE}.trans_currency_code
  - dimension: refunded
    sql: ${TABLE}.refunded
  - dimension: subaccount
    sql: ${TABLE}.subaccount
  - dimension: platform
    sql: ${TABLE}.platform
  - dimension: customer_id
    sql: ${TABLE}.customer_id
  - dimension: ship_cost
    type: number
    label: Ship Cost (Trans)
    sql: ${TABLE}.ship_cost
  - dimension: ship_cost_vat
    type: number
    hidden: true
    label: Ship Cost VAT (Trans)
    sql: ${ship_cost} * ${vat_factor_customer}
  - dimension: ship_cost_less_vat
    type: number
    hidden: true
    sql: ${ship_cost} - ${ship_cost_vat}
  - dimension: ship_cost_gbp
    type: number
    label: Ship Cost (GBP)
    sql: ${ship_cost} / ${txn_rate_to_gbp}
  - dimension: ship_cost_vat_gbp
    type: number
    hidden: true
    label: Ship Cost VAT (GBP)
    sql: ${ship_cost_gbp} * ${vat_factor_customer}
  - dimension: ship_cost_usd
    type: number
    label: Ship Cost (USD)
    sql: (
  IF(${trans_currency_code} = 'USD', ${ship_cost},
    IF(${currency_gbp_to_usd.rate} IS NULL, ${ship_cost_gbp} * 1.60,
    1.0 * ${ship_cost_gbp} * ${currency_gbp_to_usd.rate}))
)
  - dimension: ship_cost_vat_usd
    type: number
    hidden: true
    label: Ship Cost VAT (USD)
    sql: ${ship_cost_usd} * ${vat_factor_customer}
  - dimension: refund_type
    sql: CASE
  WHEN ${ship_cost} = ${ship_cost_refund_amount} THEN "Full Shipping Refund"
  WHEN ${ship_cost_refund_amount} = 0 THEN "No Refund"
  WHEN ${ship_cost} = ${ship_cost_refund_amount} IS NULL THEN "No Refund"
  ELSE "Partial Shipping Refund"
END
  - dimension: ship_cost_refund_amount
    type: number
    hidden: true
    sql: 1.0 * IFNULL(((${order_refund_fperformances.shipment_refund_amount})),0)
  - dimension: ship_cost_refund_amount_gbp
    type: number
    hidden: true
    sql: 1.0 * IFNULL(((${order_refund_fperformances.shipment_refund_amount}) / ${txn_rate_to_gbp}),0)
  - dimension: ship_cost_refund_amount_usd
    type: number
    hidden: true
    sql: IF(${order_refund_fperformances.currency_id} = 3, (${order_refund_fperformances.shipment_refund_amount}) ,${ship_cost_refund_amount_gbp} * ${txn_rate_to_usd})
  - dimension: ship_cost_less_refunds_gbp
    type: number
    hidden: true
    label: Ship Cost Less Refunds (GBP)
    sql: ${ship_cost_gbp} - ${ship_cost_refund_amount_gbp}
  - dimension: ship_cost_less_refunds_usd
    type: number
    hidden: true
    label: Ship Cost Less Refunds (USD)
    value_format: '$#,##0.00'
    sql: ${ship_cost_usd} - ${ship_cost_refund_amount_usd}
  - dimension: refunded_ship_cost_less_vat
    type: number
    label: 'Refunded: Ship Cost Less VAT (List)'
    description: This is the amount of Ship Cost refunded less VAT
    sql: if(${ship_cost} = ${ship_cost_refund_amount}, ${ship_cost_less_vat}, 0)
  - dimension: refunded_ship_cost_vat
    type: number
    label: 'Refunded: Ship Cost VAT (List)'
    sql: if(${ship_cost} = ${ship_cost_refund_amount}, ${ship_cost_vat}, 0)
  - dimension_group: dispatch
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DispatchDate
  - dimension_group: transaction
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    - month_num
    sql: ${TABLE}.transaction_date
  - measure: order_count
    type: count_distinct
    label: Unique Order Count
    sql: ${order_id}
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_ship_cost
    type: sum
    label: Total Ship Cost (Trans)
    sql: ${ship_cost}
  - measure: total_ship_cost_vat
    type: sum
    label: Total Ship Cost VAT (Trans)
    sql: ${ship_cost_vat}
  - measure: total_ship_cost_less_vat
    type: sum
    label: Total Ship Cost Less VAT (Trans)
    sql: ${ship_cost_less_vat}
  - measure: total_ship_cost_gbp
    type: sum
    label: Total Ship Cost (GBP)
    sql: ${ship_cost_gbp}
  - measure: avg_ship_cost_gbp
    type: average
    label: Avg Ship Cost (GBP)
    sql: ${ship_cost_gbp}
  - measure: total_ship_cost_vat_gbp
    type: sum
    label: Total Ship Cost VAT (GBP)
    sql: ${ship_cost_vat_gbp}
  - measure: total_ship_cost_usd
    type: sum
    label: Total Ship Cost (USD)
    value_format: '$#,##0.00'
    sql: ${ship_cost_usd}
  - measure: avg_ship_cost_usd
    type: average
    label: Avg Ship Cost (USD)
    value_format: '$#,##0.00'
    sql: ${ship_cost_usd}
  - measure: total_ship_cost_vat_usd
    type: sum
    label: Total Ship Cost VAT (USD)
    sql: ${ship_cost_vat_usd}
  - measure: total_ship_cost_refund_amount
    type: sum
    label: Total Ship Cost Refunded (Trans)
    sql: ${ship_cost_refund_amount}
  - measure: total_ship_cost_refund_amount_gbp
    type: sum
    label: Total Ship Cost Refunded (GBP)
    sql: ${ship_cost_refund_amount_gbp}
  - measure: total_ship_cost_refund_amount_usd
    type: sum
    label: Total Ship Cost Refunded (USD)
    value_format: '$#,##0.00'
    sql: ${ship_cost_refund_amount_usd}
  - measure: total_ship_cost_less_refunds_gbp
    type: sum
    label: Total Ship Cost Less Refunds (GBP)
    sql: ${ship_cost_less_refunds_gbp}
  - measure: total_ship_cost_less_refunds_usd
    type: sum
    label: Total Ship Cost Less Refunds (USD)
    value_format: '$#,##0.00'
    sql: ${ship_cost_less_refunds_usd}
  - measure: total_refunded_ship_cost_less_vat
    type: sum
    label: 'Refunded: Total Ship Cost Less VAT (List)'
    description: This is the amount of Ship Cost refunded less VAT
    sql: ${refunded_ship_cost_less_vat}
  - measure: total_refunded_ship_cost_vat
    type: sum
    label: 'Refunded: Total Ship Cost VAT (List)'
    sql: ${refunded_ship_cost_vat}