Loan Months

view: loan_months {
  sql_table_name: demo_company.sample_service ;;

  dimension: id_loan {
    hidden: yes
    sql: ${TABLE}.id_loan ;;
  }

  dimension: period_at {
    hidden: yes
    sql: ${TABLE}.period ;;
  }

  dimension: loan_age {
    label: "Current Loan Age"
    type: number
    sql: ${TABLE}.loan_age ;;
  }

  dimension: act_endg_upb {
    label: "Current Balance (upb)"
    type: number
    sql: ${TABLE}.act_endg_upb ;;
  }

  dimension: new_int_rt {
    label: "Current Coupon"
    type: number
    sql: ${TABLE}.new_int_rt ;;
  }

  dimension: delq_sts {
    label: "Delinquency Status"
    sql: ${TABLE}.delq_sts ;;
  }

  dimension: is_delinquent {
    label: "Delinquency Indicator"
    type: yesno
    sql: ${TABLE}.delq_sts > 0 AND ${TABLE}.delq_sts <= 6 ;;
  }

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

  dimension: is_prepaid {
    label: "Prepayment Indicator"
    type: yesno
    sql: ${cd_zero_bal} = 1 ;;
  }

  dimension: is_credit_event {
    type: yesno
    hidden: yes
    sql: ${cd_zero_bal} IN (2, 3, 4, 8, 97) ;;
  }

  dimension: is_foreclosure {
    type: yesno
    hidden: yes
    sql: ${cd_zero_bal} = 4 ;;
  }

  dimension: days_since_first_delinquency {
    type: number
    hidden: yes
    sql: DATEDIFF(
  MONTH,
  to_date(${orig_svcg_facts.first_reported_delinquency_month}, 'YYYY-MM-DD'),
  ${TABLE}.period
) * 30 ;;
  }

  dimension: days_since_first_delinquency_tier {
    type: tier
    hidden: yes
    sql: ${days_since_first_delinquency} ;;
  }

  dimension: flag_mod {
    type: yesno
    hidden: yes
    sql: ${TABLE}.flag_mod ;;
  }

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

  dimension: repch_flag {
    type: yesno
    hidden: yes
    sql: ${TABLE}.repch_flag ;;
  }

  dimension_group: period {
    label: "Date"
    type: time
    timeframes: [year, month]
    sql: ${TABLE}.period ;;
  }

  dimension_group: dt_zero_bal {
    type: time
    timeframes: [year, month]
    sql: trunc(dateadd(day,1,TO_DATE(${TABLE}.dt_zero_bal,'YYYYMM'))) ;;
  }

  measure: prepaid_count {
    type: count_distinct
    sql: ${id_loan} ;;
  }

  measure: percent_prepaid {
    label: "Prepayment Pct"
    type: number
    sql: 100.0 * ${prepaid_count} / NULLIF(${count_distinct_loans},0) ;;
  }

  measure: delinquent_count {
    label: "Delinquency Count"
    type: count_distinct
    sql: ${id_loan} ;;
  }

  measure: percent_delinquent {
    label: "Delinquency Pct"
    type: number
    sql: 100.0 * ${delinquent_count}/NULLIF(${count_distinct_loans},0) ;;
  }

  measure: count_distinct_loans {
    label: "Distinct Loans"
    type: count_distinct
    sql: ${id_loan} ;;
  }

  measure: total_act_endg_upb {
    type: sum
    sql: ${act_endg_upb} ;;
  }

  measure: credit_count {
    type: count_distinct
    hidden: yes
    sql: ${id_loan} ;;
  }

  measure: foreclosed_count {
    type: count_distinct
    hidden: yes
    sql: ${id_loan} ;;
  }

  measure: percent_foreclosed {
    type: number
    hidden: yes
    sql: 100.0 * ${foreclosed_count}/NULLIF(${count_distinct_loans},0) ;;
  }

  measure: modifications_count {
    type: count
    hidden: yes
    sql: ${id_loan} ;;
  }

  measure: post_credit_event_repurchase_count {
    type: count_distinct
    hidden: yes
    sql: ${id_loan} ;;
  }

  measure: percent_of_previous_prepaid {
    type: percent_of_previous
    hidden: yes
    sql: ${percent_prepaid} ;;
  }

  measure: percent_credit_event {
    type: number
    hidden: yes
    sql: 100.0 * ${credit_count} / NULLIF(${count_distinct_loans},0) ;;
  }

  measure: percent_modified {
    type: number
    hidden: yes
    sql: 100.0 * ${modifications_count} / NULLIF(${count_distinct_loans},0) ;;
  }

  measure: percent_post_credit_event_repurchase_count {
    type: number
    hidden: yes
    sql: 100.0 * ${post_credit_event_repurchase_count} / NULLIF(${count_distinct_loans},0) ;;
  }
}

- view: loan_months
  sql_table_name: demo_company.sample_service
  fields:
  - dimension: id_loan
    hidden: true
    sql: ${TABLE}.id_loan
  - dimension: period_at
    hidden: true
    sql: ${TABLE}.period
  - dimension: loan_age
    type: number
    label: Current Loan Age
    sql: ${TABLE}.loan_age
  - dimension: act_endg_upb
    type: number
    label: Current Balance (upb)
    sql: ${TABLE}.act_endg_upb
  - dimension: new_int_rt
    type: number
    label: Current Coupon
    sql: ${TABLE}.new_int_rt
  - dimension: delq_sts
    label: Delinquency Status
    sql: ${TABLE}.delq_sts
  - dimension: is_delinquent
    type: yesno
    label: Delinquency Indicator
    sql: ${TABLE}.delq_sts > 0 AND ${TABLE}.delq_sts <= 6
  - dimension: cd_zero_bal
    sql: ${TABLE}.cd_zero_bal
  - dimension: is_prepaid
    type: yesno
    label: Prepayment Indicator
    sql: ${cd_zero_bal} = 1
  - dimension: is_credit_event
    type: yesno
    hidden: true
    sql: ${cd_zero_bal} IN (2, 3, 4, 8, 97)
  - dimension: is_foreclosure
    type: yesno
    hidden: true
    sql: ${cd_zero_bal} = 4
  - dimension: days_since_first_delinquency
    type: number
    hidden: true
    sql: DATEDIFF(
  MONTH,
  to_date(${orig_svcg_facts.first_reported_delinquency_month}, 'YYYY-MM-DD'),
  ${TABLE}.period
) * 30
  - dimension: days_since_first_delinquency_tier
    type: tier
    hidden: true
    sql: ${days_since_first_delinquency}
  - dimension: flag_mod
    type: yesno
    hidden: true
    sql: ${TABLE}.flag_mod
  - dimension: mths_remng
    type: number
    hidden: true
    sql: ${TABLE}.mths_remng
  - dimension: repch_flag
    type: yesno
    hidden: true
    sql: ${TABLE}.repch_flag
  - dimension_group: period
    type: time
    timeframes:
    - year
    - month
    sql: ${TABLE}.period
  - dimension_group: dt_zero_bal
    type: time
    timeframes:
    - year
    - month
    sql: trunc(dateadd(day,1,TO_DATE(${TABLE}.dt_zero_bal,'YYYYMM')))
  - measure: prepaid_count
    type: count_distinct
    sql: ${id_loan}
  - measure: percent_prepaid
    type: number
    label: Prepayment Pct
    sql: 100.0 * ${prepaid_count} / NULLIF(${count_distinct_loans},0)
  - measure: delinquent_count
    type: count_distinct
    label: Delinquency Count
    sql: ${id_loan}
  - measure: percent_delinquent
    type: number
    label: Delinquency Pct
    sql: 100.0 * ${delinquent_count}/NULLIF(${count_distinct_loans},0)
  - measure: count_distinct_loans
    type: count_distinct
    label: Distinct Loans
    sql: ${id_loan}
  - measure: total_act_endg_upb
    type: sum
    sql: ${act_endg_upb}
  - measure: credit_count
    type: count_distinct
    sql: ${id_loan}
  - measure: foreclosed_count
    type: count_distinct
    sql: ${id_loan}
  - measure: percent_foreclosed
    type: number
    sql: 100.0 * ${foreclosed_count}/NULLIF(${count_distinct_loans},0)
  - measure: modifications_count
    type: count
    sql: ${id_loan}
  - measure: post_credit_event_repurchase_count
    type: count_distinct
    sql: ${id_loan}
  - measure: percent_of_previous_prepaid
    type: percent_of_previous
    sql: ${percent_prepaid}
  - measure: percent_credit_event
    type: number
    sql: 100.0 * ${credit_count} / NULLIF(${count_distinct_loans},0)
  - measure: percent_modified
    type: number
    sql: 100.0 * ${modifications_count} / NULLIF(${count_distinct_loans},0)
  - measure: percent_post_credit_event_repurchase_count
    type: number
    sql: 100.0 * ${post_credit_event_repurchase_count} / NULLIF(${count_distinct_loans},0)