Dell Asp

view: dell_asp {
  sql_table_name: DBR ;;

  dimension: dbr_no {
    primary_key: yes
    sql: ${TABLE}.DBR_NO ;;
  }

  dimension: early_stage {
    label: "EARLY STAGE"
    type: yesno
    sql: ${TABLE}.DBR_CLIENT IN ('DELL23','DELL24','DELL25') ;;
  }

  dimension: non_recoverable {
    label: "NON RECOVERABLE"
    type: yesno
    sql: ${TABLE}.DBR_STATUS in ('DSK','CON','NRP','KYH','DTF','ASP','CKP','DFD','DTR','DRP') ;;
  }

  dimension: returned {
    label: "RETURNED"
    type: yesno
    sql: ${TABLE}.DBR_STATUS in ('APR','PIF') ;;
  }

  dimension: rfc_dispatch {
    label: "RFC DISPATCH"
    type: yesno
    sql: ${adr.adr_code} = 'Y' ;;
  }

  dimension: repair_dispatch {
    label: "REPAIR DISPATCH"
    type: yesno
    sql: ${adr.adr_code} = 'N' ;;
  }

  dimension: enterprise_products {
    label: "ENTERPRISE PRODUCTS"
    type: yesno
    sql: ${TABLE}.dbr_cl_misc_3 = 'ENTERPRISE_PRODUCTS' ;;
  }

  dimension: s_p {
    label: "S_AND_P"
    type: yesno
    sql: ${TABLE}.dbr_cl_misc_3 = 'S_P' ;;
  }

  dimension: hard_drives {
    label: "HARD DRIVES"
    type: yesno
    sql: ${TABLE}.dbr_cl_misc_3 = 'HD' ;;
  }

  dimension: dbr_agency {
    sql: ${TABLE}.DBR_AGENCY ;;
  }

  dimension: dbr_assign_amt {
    type: number
    sql: ${TABLE}.DBR_ASSIGN_AMT ;;
  }

  dimension: dbr_batch_no {
    sql: ${TABLE}.DBR_BATCH_NO ;;
  }

  dimension: dbr_batch_seq {
    sql: ${TABLE}.DBR_BATCH_SEQ ;;
  }

  dimension: dbr_category {
    sql: ${TABLE}.DBR_CATEGORY ;;
  }

  dimension: dbr_cl_codes_1 {
    sql: ${TABLE}.DBR_CL_CODES_1 ;;
  }

  dimension: dbr_cl_codes_2 {
    sql: ${TABLE}.DBR_CL_CODES_2 ;;
  }

  dimension: dbr_cl_codes_3 {
    sql: ${TABLE}.DBR_CL_CODES_3 ;;
  }

  dimension: dbr_cl_codes_4 {
    sql: ${TABLE}.DBR_CL_CODES_4 ;;
  }

  dimension: dbr_cl_misc_1 {
    sql: ${TABLE}.DBR_CL_MISC_1 ;;
  }

  dimension: dbr_cl_misc_2 {
    sql: ${TABLE}.DBR_CL_MISC_2 ;;
  }

  dimension: dbr_cl_misc_3 {
    label: "COMMODITY TEAM"
    sql: ${TABLE}.DBR_CL_MISC_3 ;;
  }

  dimension: dbr_cl_misc_4 {
    sql: ${TABLE}.DBR_CL_MISC_4 ;;
  }

  dimension: dbr_class {
    sql: ${TABLE}.DBR_CLASS ;;
  }

  dimension: dbr_class_desc {
    label: "ACCOUNT CLASS"
  }

  dimension: dbr_cli_ref_no {
    sql: ${TABLE}.DBR_CLI_REF_NO ;;
  }

  dimension: dbr_client {
    sql: ${TABLE}.DBR_CLIENT ;;
  }

  dimension: dbr_client_int_amt {
    type: number
    sql: ${TABLE}.DBR_CLIENT_INT_AMT ;;
  }

  dimension: dbr_com_rate {
    type: number
    sql: ${TABLE}.DBR_COM_RATE ;;
  }

  dimension: dbr_com_rule {
    type: number
    sql: ${TABLE}.DBR_COM_RULE ;;
  }

  dimension: dbr_int_due {
    type: number
    sql: ${TABLE}.DBR_INT_DUE ;;
  }

  dimension: dbr_int_rate {
    type: number
    sql: ${TABLE}.DBR_INT_RATE ;;
  }

  dimension: dbr_int_tot {
    type: number
    sql: ${TABLE}.DBR_INT_TOT ;;
  }

  dimension: dbr_name1 {
    sql: ${TABLE}.DBR_NAME1 ;;
  }

  dimension: dbr_next_ltr_no {
    type: number
    sql: ${TABLE}.DBR_NEXT_LTR_NO ;;
  }

  dimension: dbr_other_due {
    type: number
    sql: ${TABLE}.DBR_OTHER_DUE ;;
  }

  dimension: dbr_other_tot {
    type: number
    sql: ${TABLE}.DBR_OTHER_TOT ;;
  }

  dimension: dbr_owner {
    sql: ${TABLE}.DBR_OWNER ;;
  }

  dimension: dbr_principal_due {
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_DUE ;;
  }

  dimension: dbr_principal_recvd {
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_RECVD ;;
  }

  dimension: dbr_priority {
    sql: ${TABLE}.DBR_PRIORITY ;;
  }

  dimension: dbr_recvd_tot {
    type: number
    sql: ${TABLE}.DBR_RECVD_TOT ;;
  }

  dimension: dbr_status {
    sql: ${TABLE}.DBR_STATUS ;;
  }

  dimension: dbr_tie_series {
    sql: ${TABLE}.DBR_TIE_SERIES ;;
  }

  dimension: dbr_time_zone {
    sql: ${TABLE}.DBR_TIME_ZONE ;;
  }

  dimension_group: dbr_assign_date_o {
    label: "PLACEMENT"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_ASSIGN_DATE_O ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  measure: total_assign_amt {
    label: "TOTAL PLACED"
    type: sum
    sql: ${dbr_assign_amt} ;;
  }

  measure: count {
    label: "ACCOUNTS PLACED"
    type: count
  }

  measure: total_dbr_assign_amt {
    label: "ASSIGNED AMOUNT"
    type: sum
    sql: ${dbr_assign_amt} ;;
  }

  measure: total_recoverable_assign_amt {
    label: "RECOVERABLE AMOUNT"
    type: sum
    filters: [non_recoverable: "No"]
    sql: ${dbr_assign_amt} ;;
  }

  measure: total_non_recoverable_assign_amt {
    label: "NON RECOVERABLE AMOUNT"
    type: sum
    filters: [non_recoverable: "Yes"]
    sql: ${dbr_assign_amt} ;;
  }

  measure: returned_amt {
    label: "RETURNED AMOUNT"
    type: sum
    filters: [returned: "Yes"]
    sql: ${dbr_assign_amt} ;;
  }

  measure: total_returned_percent {
    label: "TOTAL RETURNED PERCENT"
    type: number
    sql: ${returned_amt}/ NULLIF(${total_recoverable_assign_amt},0) * 100.0 ;;
  }

  measure: count_active {
    type: count
    filters: [dbr_class_desc: "Active"]
  }

  measure: percent_active {
    type: number
    sql: 100.0 * ${count_active}/ NULLIF(${count},0) ;;
  }
}

- view: dell_asp
  sql_table_name: DBR
  fields:
  - dimension: dbr_no
    primary_key: true
    sql: ${TABLE}.DBR_NO
  - dimension: early_stage
    type: yesno
    label: EARLY STAGE
    sql: ${TABLE}.DBR_CLIENT IN ('DELL23','DELL24','DELL25')
  - dimension: non_recoverable
    type: yesno
    label: NON RECOVERABLE
    sql: ${TABLE}.DBR_STATUS in ('DSK','CON','NRP','KYH','DTF','ASP','CKP','DFD','DTR','DRP')
  - dimension: returned
    type: yesno
    label: RETURNED
    sql: ${TABLE}.DBR_STATUS in ('APR','PIF')
  - dimension: rfc_dispatch
    type: yesno
    label: RFC DISPATCH
    sql: ${adr.adr_code} = 'Y'
  - dimension: repair_dispatch
    type: yesno
    label: REPAIR DISPATCH
    sql: ${adr.adr_code} = 'N'
  - dimension: enterprise_products
    type: yesno
    label: ENTERPRISE PRODUCTS
    sql: ${TABLE}.dbr_cl_misc_3 = 'ENTERPRISE_PRODUCTS'
  - dimension: s_p
    type: yesno
    label: S_AND_P
    sql: ${TABLE}.dbr_cl_misc_3 = 'S_P'
  - dimension: hard_drives
    type: yesno
    label: HARD DRIVES
    sql: ${TABLE}.dbr_cl_misc_3 = 'HD'
  - dimension: dbr_agency
    sql: ${TABLE}.DBR_AGENCY
  - dimension: dbr_assign_amt
    type: number
    sql: ${TABLE}.DBR_ASSIGN_AMT
  - dimension: dbr_batch_no
    sql: ${TABLE}.DBR_BATCH_NO
  - dimension: dbr_batch_seq
    sql: ${TABLE}.DBR_BATCH_SEQ
  - dimension: dbr_category
    sql: ${TABLE}.DBR_CATEGORY
  - dimension: dbr_cl_codes_1
    sql: ${TABLE}.DBR_CL_CODES_1
  - dimension: dbr_cl_codes_2
    sql: ${TABLE}.DBR_CL_CODES_2
  - dimension: dbr_cl_codes_3
    sql: ${TABLE}.DBR_CL_CODES_3
  - dimension: dbr_cl_codes_4
    sql: ${TABLE}.DBR_CL_CODES_4
  - dimension: dbr_cl_misc_1
    sql: ${TABLE}.DBR_CL_MISC_1
  - dimension: dbr_cl_misc_2
    sql: ${TABLE}.DBR_CL_MISC_2
  - dimension: dbr_cl_misc_3
    label: COMMODITY TEAM
    sql: ${TABLE}.DBR_CL_MISC_3
  - dimension: dbr_cl_misc_4
    sql: ${TABLE}.DBR_CL_MISC_4
  - dimension: dbr_class
    sql: ${TABLE}.DBR_CLASS
  - dimension: dbr_class_desc
    label: ACCOUNT CLASS
  - dimension: dbr_cli_ref_no
    sql: ${TABLE}.DBR_CLI_REF_NO
  - dimension: dbr_client
    sql: ${TABLE}.DBR_CLIENT
  - dimension: dbr_client_int_amt
    type: number
    sql: ${TABLE}.DBR_CLIENT_INT_AMT
  - dimension: dbr_com_rate
    type: number
    sql: ${TABLE}.DBR_COM_RATE
  - dimension: dbr_com_rule
    type: number
    sql: ${TABLE}.DBR_COM_RULE
  - dimension: dbr_int_due
    type: number
    sql: ${TABLE}.DBR_INT_DUE
  - dimension: dbr_int_rate
    type: number
    sql: ${TABLE}.DBR_INT_RATE
  - dimension: dbr_int_tot
    type: number
    sql: ${TABLE}.DBR_INT_TOT
  - dimension: dbr_name1
    sql: ${TABLE}.DBR_NAME1
  - dimension: dbr_next_ltr_no
    type: number
    sql: ${TABLE}.DBR_NEXT_LTR_NO
  - dimension: dbr_other_due
    type: number
    sql: ${TABLE}.DBR_OTHER_DUE
  - dimension: dbr_other_tot
    type: number
    sql: ${TABLE}.DBR_OTHER_TOT
  - dimension: dbr_owner
    sql: ${TABLE}.DBR_OWNER
  - dimension: dbr_principal_due
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_DUE
  - dimension: dbr_principal_recvd
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_RECVD
  - dimension: dbr_priority
    sql: ${TABLE}.DBR_PRIORITY
  - dimension: dbr_recvd_tot
    type: number
    sql: ${TABLE}.DBR_RECVD_TOT
  - dimension: dbr_status
    sql: ${TABLE}.DBR_STATUS
  - dimension: dbr_tie_series
    sql: ${TABLE}.DBR_TIE_SERIES
  - dimension: dbr_time_zone
    sql: ${TABLE}.DBR_TIME_ZONE
  - dimension_group: dbr_assign_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_ASSIGN_DATE_O
  - dimension_group: dbr_cl_dates_1_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_1_O
  - dimension_group: dbr_cl_dates_2_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_2_O
  - dimension_group: dbr_cl_dates_3_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_3_O
  - dimension_group: dbr_cl_dates_4_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_4_O
  - dimension_group: dbr_close_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CLOSE_DATE_O
  - dimension_group: dbr_int_thru_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_INT_THRU_DATE_O
  - dimension_group: dbr_last_chg_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_CHG_DATE_O
  - dimension_group: dbr_last_letter_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_LETTER_O
  - dimension_group: dbr_last_pay_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_PAY_DATE_O
  - dimension_group: dbr_last_pdc_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_PDC_DATE_O
  - dimension_group: dbr_last_trust_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_TRUST_DATE_O
  - dimension_group: dbr_last_worked_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_WORKED_O
  - dimension_group: dbr_next_ltr_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_NEXT_LTR_DATE_O
  - dimension_group: dbr_pcasgn_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_PCASGN_DATE_O
  - dimension_group: dbr_review_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_REVIEW_DATE_O
  - measure: total_assign_amt
    type: sum
    label: TOTAL PLACED
    sql: ${dbr_assign_amt}
  - measure: count
    type: count
    label: ACCOUNTS PLACED
  - measure: total_dbr_assign_amt
    type: sum
    label: ASSIGNED AMOUNT
    sql: ${dbr_assign_amt}
  - measure: total_recoverable_assign_amt
    type: sum
    label: RECOVERABLE AMOUNT
    filters:
    - non_recoverable: No
    sql: ${dbr_assign_amt}
  - measure: total_non_recoverable_assign_amt
    type: sum
    label: NON RECOVERABLE AMOUNT
    filters:
    - non_recoverable: Yes
    sql: ${dbr_assign_amt}
  - measure: returned_amt
    type: sum
    label: RETURNED AMOUNT
    filters:
    - returned: Yes
    sql: ${dbr_assign_amt}
  - measure: total_returned_percent
    type: number
    label: TOTAL RETURNED PERCENT
    sql: ${returned_amt}/ NULLIF(${total_recoverable_assign_amt},0) * 100.0
  - measure: count_active
    type: count
    filters:
    - dbr_class_desc: Active
  - measure: percent_active
    type: number
    sql: 100.0 * ${count_active}/ NULLIF(${count},0)