Ae Loans

view: ae_loans {
  sql_table_name: public.ae_loans ;;

  dimension: amtbill {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}.amtbill ;;
  }

  dimension: amtcumulativechargedofffee {
    type: number
    sql: ${TABLE}.amtcumulativechargedofffee ;;
  }

  dimension: amtcumulativechargedoffprincipal {
    type: number
    sql: ${TABLE}.amtcumulativechargedoffprincipal ;;
  }

  dimension: amtcumulativechargedofftotal {
    type: number
    sql: ${TABLE}.amtcumulativechargedofftotal ;;
  }

  dimension: amtcumulativewaivedfee {
    type: number
    sql: ${TABLE}.amtcumulativewaivedfee ;;
  }

  dimension: amtcumulativewaivedinterest {
    type: number
    sql: ${TABLE}.amtcumulativewaivedinterest ;;
  }

  dimension: amtcumulativewaivedprincipal {
    type: number
    sql: ${TABLE}.amtcumulativewaivedprincipal ;;
  }

  dimension: amtcumulativewaivedtotal {
    type: number
    sql: ${TABLE}.amtcumulativewaivedtotal ;;
  }

  dimension: amtfeeorig {
    type: number
    sql: ${TABLE}.amtfeeorig ;;
  }

  dimension: amtfuturepayments {
    type: number
    sql: ${TABLE}.amtfuturepayments ;;
  }

  dimension: amtloan {
    type: number
    sql: ${TABLE}.amtloan ;;
  }

  dimension: amtpaidint {
    type: number
    sql: ${TABLE}.amtpaidint ;;
  }

  dimension: amtpaidlate {
    type: number
    sql: ${TABLE}.amtpaidlate ;;
  }

  dimension: amtpaidprin {
    type: number
    sql: ${TABLE}.amtpaidprin ;;
  }

  dimension: amtpaidtotal {
    type: number
    sql: ${TABLE}.amtpaidtotal ;;
  }

  dimension: balbehind {
    type: number
    sql: ${TABLE}.balbehind ;;
  }

  dimension: baldq {
    type: number
    sql: ${TABLE}.baldq ;;
  }

  dimension: balint {
    type: number
    sql: ${TABLE}.balint ;;
  }

  dimension: ballate {
    type: number
    sql: ${TABLE}.ballate ;;
  }

  dimension: balprin {
    type: number
    sql: ${TABLE}.balprin ;;
  }

  dimension: baltotal {
    type: number
    sql: ${TABLE}.baltotal ;;
  }

  dimension: cntdaysactive {
    type: number
    sql: ${TABLE}.cntdaysactive ;;
  }

  dimension: cntdayspastdue {
    type: number
    sql: ${TABLE}.cntdayspastdue ;;
  }

  dimension: cntdelinquencies {
    type: number
    sql: ${TABLE}.cntdelinquencies ;;
  }

  dimension: cntpmtsattempted {
    type: number
    sql: ${TABLE}.cntpmtsattempted ;;
  }

  dimension: cntpmtsdeferred {
    type: number
    sql: ${TABLE}.cntpmtsdeferred ;;
  }

  dimension: cntpmtsmade {
    type: number
    sql: ${TABLE}.cntpmtsmade ;;
  }

  dimension: cntpmtsmissed {
    type: number
    sql: ${TABLE}.cntpmtsmissed ;;
  }

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

  dimension: flgautodebit {
    type: number
    sql: ${TABLE}.flgautodebit ;;
  }

  dimension: flgfpf {
    type: number
    sql: ${TABLE}.flgfpf ;;
  }

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

  dimension: flgsold {
    type: number
    sql: ${TABLE}.flgsold ;;
  }

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

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

  dimension: idcsr {
    type: number
    sql: ${TABLE}.idcsr ;;
  }

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

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

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

  dimension: rtinterestcurrent {
    type: number
    sql: ${TABLE}.rtinterestcurrent ;;
  }

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

  dimension: first_pmt_vintage {
    description: "Puts the dtPmtFirst field into Vintage format"
    type: string
    sql: SUBSTRING(dtpmtfirst from 3 for 2) || 'M' || SUBSTRING(dtpmtfirst from 6 for 2) ;;
  }

  dimension_group: dtchargeoff {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtchargeoff ;;
  }

  dimension_group: dtdisbursement {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtdisbursement ;;
  }

  dimension_group: dtfirstdq {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtfirstdq ;;
  }

  dimension_group: dtlastmadepayment {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtlastmadepayment ;;
  }

  dimension_group: dtlastmissedpayment {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtlastmissedpayment ;;
  }

  dimension_group: dtloan {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtloan ;;
  }

  dimension_group: dtpmtfirst {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtpmtfirst ;;
  }

  dimension_group: dtpmtlast {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtpmtlast ;;
  }

  dimension_group: dtpmtmissedfirst {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtpmtmissedfirst ;;
  }

  dimension_group: dtpmtsettlelast {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtpmtsettlelast ;;
  }

  dimension_group: dtzerobalance {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.dtzerobalance ;;
  }

  measure: count {
    type: count
  }

  measure: avg_new_fpf {
    description: "Calculates the mean for New Borrowers"
    type: average
    filters: [ae_applications.is_repeat_borrower: "False"]
    drill_fields: [borrowers]
    sql: 100.0 * ${flgfpf} ;;
  }

  measure: avg_repeat_fpf {
    description: "Calculates the mean for Repeat Borrowers"
    type: average
    filters: [ae_applications.is_repeat_borrower: "True"]
    drill_fields: [borrowers]
    sql: 100.0 * ${flgfpf} ;;
  }

  measure: avg_all_fpf {
    description: "Calculates the mean for All Borrowers"
    type: average
    drill_fields: [borrowers]
    sql: 100.0 * ${flgfpf} ;;
  }

  measure: by_first_pmt {
    description: "Calculates the mean for All Borrowers that have a first payment"
    type: average
    filters: [dtpmtfirst_date: "-NULL"]
    sql: 100.0 * ${flgfpf} ;;
  }

  measure: sum_flgfpf {
    description: "Sum of the number of first payment failures (non null 1st payment date)"
    type: sum
    filters: [dtpmtfirst_date: "-NULL"]
    sql: ${flgfpf} ;;
  }

  measure: count_flgfpf {
    description: "Count of the number of first payment failures (non null 1st payment date)"
    type: count
    filters: [dtpmtfirst_date: "-NULL"]
  }
}

- view: ae_loans
  sql_table_name: public.ae_loans
  fields:
  - dimension: amtbill
    type: number
    value_format: $0.00
    sql: ${TABLE}.amtbill
  - dimension: amtcumulativechargedofffee
    type: number
    sql: ${TABLE}.amtcumulativechargedofffee
  - dimension: amtcumulativechargedoffprincipal
    type: number
    sql: ${TABLE}.amtcumulativechargedoffprincipal
  - dimension: amtcumulativechargedofftotal
    type: number
    sql: ${TABLE}.amtcumulativechargedofftotal
  - dimension: amtcumulativewaivedfee
    type: number
    sql: ${TABLE}.amtcumulativewaivedfee
  - dimension: amtcumulativewaivedinterest
    type: number
    sql: ${TABLE}.amtcumulativewaivedinterest
  - dimension: amtcumulativewaivedprincipal
    type: number
    sql: ${TABLE}.amtcumulativewaivedprincipal
  - dimension: amtcumulativewaivedtotal
    type: number
    sql: ${TABLE}.amtcumulativewaivedtotal
  - dimension: amtfeeorig
    type: number
    sql: ${TABLE}.amtfeeorig
  - dimension: amtfuturepayments
    type: number
    sql: ${TABLE}.amtfuturepayments
  - dimension: amtloan
    type: number
    sql: ${TABLE}.amtloan
  - dimension: amtpaidint
    type: number
    sql: ${TABLE}.amtpaidint
  - dimension: amtpaidlate
    type: number
    sql: ${TABLE}.amtpaidlate
  - dimension: amtpaidprin
    type: number
    sql: ${TABLE}.amtpaidprin
  - dimension: amtpaidtotal
    type: number
    sql: ${TABLE}.amtpaidtotal
  - dimension: balbehind
    type: number
    sql: ${TABLE}.balbehind
  - dimension: baldq
    type: number
    sql: ${TABLE}.baldq
  - dimension: balint
    type: number
    sql: ${TABLE}.balint
  - dimension: ballate
    type: number
    sql: ${TABLE}.ballate
  - dimension: balprin
    type: number
    sql: ${TABLE}.balprin
  - dimension: baltotal
    type: number
    sql: ${TABLE}.baltotal
  - dimension: cntdaysactive
    type: number
    sql: ${TABLE}.cntdaysactive
  - dimension: cntdayspastdue
    type: number
    sql: ${TABLE}.cntdayspastdue
  - dimension: cntdelinquencies
    type: number
    sql: ${TABLE}.cntdelinquencies
  - dimension: cntpmtsattempted
    type: number
    sql: ${TABLE}.cntpmtsattempted
  - dimension: cntpmtsdeferred
    type: number
    sql: ${TABLE}.cntpmtsdeferred
  - dimension: cntpmtsmade
    type: number
    sql: ${TABLE}.cntpmtsmade
  - dimension: cntpmtsmissed
    type: number
    sql: ${TABLE}.cntpmtsmissed
  - dimension: dttranche
    sql: ${TABLE}.dttranche
  - dimension: flgautodebit
    type: number
    sql: ${TABLE}.flgautodebit
  - dimension: flgfpf
    type: number
    sql: ${TABLE}.flgfpf
  - dimension: flggood
    sql: ${TABLE}.flggood
  - dimension: flgsold
    type: number
    sql: ${TABLE}.flgsold
  - dimension: flgstatus
    sql: ${TABLE}.flgstatus
  - dimension: flgstatussimple
    sql: ${TABLE}.flgstatussimple
  - dimension: idcsr
    type: number
    sql: ${TABLE}.idcsr
  - dimension: idcust
    sql: ${TABLE}.idcust
  - dimension: idloan
    primary_key: true
    sql: ${TABLE}.idloan
  - dimension: pmtfreq
    sql: ${TABLE}.pmtfreq
  - dimension: rtinterestcurrent
    type: number
    sql: ${TABLE}.rtinterestcurrent
  - dimension: txtcsr
    sql: ${TABLE}.txtcsr
  - dimension: first_pmt_vintage
    type: string
    description: Puts the dtPmtFirst field into Vintage format
    sql: SUBSTRING(dtpmtfirst from 3 for 2) || 'M' || SUBSTRING(dtpmtfirst from 6 for 2)
  - dimension_group: dtchargeoff
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtchargeoff
  - dimension_group: dtdisbursement
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtdisbursement
  - dimension_group: dtfirstdq
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtfirstdq
  - dimension_group: dtlastmadepayment
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtlastmadepayment
  - dimension_group: dtlastmissedpayment
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtlastmissedpayment
  - dimension_group: dtloan
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtloan
  - dimension_group: dtpmtfirst
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtpmtfirst
  - dimension_group: dtpmtlast
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtpmtlast
  - dimension_group: dtpmtmissedfirst
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtpmtmissedfirst
  - dimension_group: dtpmtsettlelast
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtpmtsettlelast
  - dimension_group: dtzerobalance
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.dtzerobalance
  - measure: count
    type: count
  - measure: avg_new_fpf
    type: average
    description: Calculates the mean for New Borrowers
    drill_fields:
    - borrowers
    filters:
    - ae_applications.is_repeat_borrower: False
    sql: 100.0 * ${flgfpf}
  - measure: avg_repeat_fpf
    type: average
    description: Calculates the mean for Repeat Borrowers
    drill_fields:
    - borrowers
    filters:
    - ae_applications.is_repeat_borrower: True
    sql: 100.0 * ${flgfpf}
  - measure: avg_all_fpf
    type: average
    description: Calculates the mean for All Borrowers
    drill_fields:
    - borrowers
    sql: 100.0 * ${flgfpf}
  - measure: by_first_pmt
    type: average
    description: Calculates the mean for All Borrowers that have a first payment
    filters:
    - dtpmtfirst_date: -NULL
    sql: 100.0 * ${flgfpf}
  - measure: sum_flgfpf
    type: sum
    description: Sum of the number of first payment failures (non null 1st payment date)
    filters:
    - dtpmtfirst_date: -NULL
    sql: ${flgfpf}
  - measure: count_flgfpf
    type: count
    description: Count of the number of first payment failures (non null 1st payment date)
    filters:
    - dtpmtfirst_date: -NULL