SQL Genomics health services

Final Allowed

Final Allowed model for genomics health services.

source-preserved

Blocks

dbt SQL LKML LookML

final_allowed.sql · 94 lines

inferred

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

SELECT
  o.order_id AS order_id
  , a.ASP AS actual_asp
  , p.projected_insurance_asp_for_join as projected_insurance_asp
  , o.bill_type as bill_type
  , terminal_state.terminal_bool as terminal_bool
  , a.completed_on
  , o.status_name
  , concat(o.product,o.methodology) as product_method
  ,a.Comp_Amt
  ,a.Customer_Inv_Amt
  ,a.Physician_Inv_Amt
  ,a.payer_paid
  ,a.Transfer_Amt
  ,a.ASP
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN terminal_state.terminal_bool = FALSE THEN
      CASE WHEN o.status_name = 'Benefits Researched' THEN
        CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120
          ELSE p.projected_insurance_asp_for_join
          END
        ELSE p.projected_insurance_asp_for_join
        END
      ELSE a.ASP
      END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    ELSE a.ASP
    END AS adjusted_allowed
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN o.status_name = 'Benefits Researched' THEN
       CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120 ELSE NULL END
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END as adjusted_allowed_term_only
FROM (
  SELECT
    "order".id AS order_id
    ,"order".product AS product
    ,"order".disease_panel AS disease_panel
    ,"order".testing_methodology AS methodology
    ,"order".bill_type as bill_type
    ,"order".account_identifier as account_identifier
    ,"order".price as price
    ,TO_CHAR("order".completed_on, 'YYYY-MM') AS completed_month
    ,insuranceclaim.payer_identifier as payer_identifier
    ,insuranceclaim.plan_name as plan_name
    ,insuranceclaim.status_name as status_name
  FROM  ${SCHEMA}.order
  LEFT JOIN  ${SCHEMA}.insuranceclaim AS insuranceclaim ON insuranceclaim.order_id = "order".id
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11) AS o
LEFT JOIN {{ ref('allowed_by_order') }} AS a ON a.id = o.order_id
LEFT JOIN {{ ref('terminal_state') }} AS terminal_state ON terminal_state.id = o.order_id
LEFT JOIN {{ ref('projection') }} AS p ON p.product = o.product
  AND o.methodology = p.methodology
  AND o.disease_panel = p.disease_panel
  AND coalesce(o.payer_identifier,'blank') = coalesce(p.payer_identifier,'blank')
  AND coalesce(o.plan_name,'blank') = coalesce(p.plan_name,'blank')
  AND o.completed_month = p.completed_month
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

# models/schema.yml
version: 2

models:
  - name: final_allowed
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: bill_type
      - name: actual_asp
      - name: projected_asp
      - name: terminal_bool
      - name: payer_paid
      - name: Customer_Inv_Amt
      - name: Physician_Inv_Amt
      - name: Transfer_Amt
      - name: Comp_Amt
      - name: unadjusted_asp
      - name: adjusted_asp

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
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.

final_allowed.sql · 69 lines

source-preserved validation: not checked

SELECT
  o.order_id AS order_id
  , a.ASP AS actual_asp
  , p.projected_insurance_asp_for_join as projected_insurance_asp
  , o.bill_type as bill_type
  , terminal_state.terminal_bool as terminal_bool
  , a.completed_on
  , o.status_name
  , concat(o.product,o.methodology) as product_method
  ,a.Comp_Amt
  ,a.Customer_Inv_Amt
  ,a.Physician_Inv_Amt
  ,a.payer_paid
  ,a.Transfer_Amt
  ,a.ASP
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN terminal_state.terminal_bool = FALSE THEN
      CASE WHEN o.status_name = 'Benefits Researched' THEN
        CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120
          ELSE p.projected_insurance_asp_for_join
          END
        ELSE p.projected_insurance_asp_for_join
        END
      ELSE a.ASP
      END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    ELSE a.ASP
    END AS adjusted_allowed
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN o.status_name = 'Benefits Researched' THEN
       CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120 ELSE NULL END
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END as adjusted_allowed_term_only
FROM (
  SELECT
    "order".id AS order_id
    ,"order".product AS product
    ,"order".disease_panel AS disease_panel
    ,"order".testing_methodology AS methodology
    ,"order".bill_type as bill_type
    ,"order".account_identifier as account_identifier
    ,"order".price as price
    ,TO_CHAR("order".completed_on, 'YYYY-MM') AS completed_month
    ,insuranceclaim.payer_identifier as payer_identifier
    ,insuranceclaim.plan_name as plan_name
    ,insuranceclaim.status_name as status_name
  FROM  ${SCHEMA}.order
  LEFT JOIN  ${SCHEMA}.insuranceclaim AS insuranceclaim ON insuranceclaim.order_id = "order".id
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11) AS o
LEFT JOIN ${allowed_by_order.SQL_TABLE_NAME} AS a ON a.id = o.order_id
LEFT JOIN ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id = o.order_id
LEFT JOIN ${projection.SQL_TABLE_NAME} AS p ON p.product = o.product
  AND o.methodology = p.methodology
  AND o.disease_panel = p.disease_panel
  AND coalesce(o.payer_identifier,'blank') = coalesce(p.payer_identifier,'blank')
  AND coalesce(o.plan_name,'blank') = coalesce(p.plan_name,'blank')
  AND o.completed_month = p.completed_month
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

final_allowed.view.lkml · 444 lines

inferred

view: final_allowed {
  derived_table: {
    sql: SELECT
  o.order_id AS order_id
  , a.ASP AS actual_asp
  , p.projected_insurance_asp_for_join as projected_insurance_asp
  , o.bill_type as bill_type
  , terminal_state.terminal_bool as terminal_bool
  , a.completed_on
  , o.status_name
  , concat(o.product,o.methodology) as product_method
  ,a.Comp_Amt
  ,a.Customer_Inv_Amt
  ,a.Physician_Inv_Amt
  ,a.payer_paid
  ,a.Transfer_Amt
  ,a.ASP
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN terminal_state.terminal_bool = FALSE THEN
      CASE WHEN o.status_name = 'Benefits Researched' THEN
        CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120
          ELSE p.projected_insurance_asp_for_join
          END
        ELSE p.projected_insurance_asp_for_join
        END
      ELSE a.ASP
      END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    ELSE a.ASP
    END AS adjusted_allowed
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN o.status_name = 'Benefits Researched' THEN
       CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120 ELSE NULL END
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END as adjusted_allowed_term_only
FROM (
  SELECT
    "order".id AS order_id
    ,"order".product AS product
    ,"order".disease_panel AS disease_panel
    ,"order".testing_methodology AS methodology
    ,"order".bill_type as bill_type
    ,"order".account_identifier as account_identifier
    ,"order".price as price
    ,TO_CHAR("order".completed_on, 'YYYY-MM') AS completed_month
    ,insuranceclaim.payer_identifier as payer_identifier
    ,insuranceclaim.plan_name as plan_name
    ,insuranceclaim.status_name as status_name
  FROM  ${SCHEMA}.order
  LEFT JOIN  ${SCHEMA}.insuranceclaim AS insuranceclaim ON insuranceclaim.order_id = "order".id
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11) AS o
LEFT JOIN ${allowed_by_order.SQL_TABLE_NAME} AS a ON a.id = o.order_id
LEFT JOIN ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id = o.order_id
LEFT JOIN ${projection.SQL_TABLE_NAME} AS p ON p.product = o.product
  AND o.methodology = p.methodology
  AND o.disease_panel = p.disease_panel
  AND coalesce(o.payer_identifier,'blank') = coalesce(p.payer_identifier,'blank')
  AND coalesce(o.plan_name,'blank') = coalesce(p.plan_name,'blank')
  AND o.completed_month = p.completed_month
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 ;;
  }

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

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

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

  dimension: projected_asp {
    hidden: yes
    sql: ${TABLE}.projected_insurance_asp ;;
  }

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

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

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

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

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

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

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

  dimension: adjusted_asp {
    type: number
    hidden: yes
    sql: CASE WHEN ${bill_type} = 'in' THEN
  CASE WHEN ${terminal_bool} = FALSE THEN
    CASE WHEN ${claim.status_name} = 'Benefits Researched' THEN
      CASE WHEN product_method = '<% parameter product_method_1 %>' THEN 99
        WHEN product_method = '<% parameter product_method_2 %>' THEN 299
        WHEN product_method = '<% parameter product_method_3 %>' THEN 299
        WHEN product_method = '<% parameter product_method_4 %>' THEN ${projected_asp}
        ELSE ${projected_asp}
        END
      ELSE ${projected_asp}
      END
    ELSE ${unadjusted_asp}
    END
    WHEN ${bill_type} = <% parameter bill_type_cnsmt %> THEN ${unadjusted_asp}
    WHEN ${bill_type} = <% parameter bill_type_cc_placeholder %> THEN ${unadjusted_asp}
  ELSE ${unadjusted_asp}
  END ;;
  }

  measure: sum_allowed {
    label: "Sum of Allowed"
    type: sum
    value_format: "$#,###"
    sql: ${adjusted_asp} ;;
  }

  measure: sum_allowed_this_month {
    label: "Sum of Allowed this month"
    type: sum
    hidden: yes
    value_format: "$#,###"
    sql: ${adjusted_asp} ;;
  }

  measure: percent_of_sum_allowed {
    label: "Percent of Total Sum of Allowed"
    type: percent_of_total
    sql: ${sum_allowed} ;;
  }

  measure: percent_of_sum_allowed_this_month {
    label: "Percent of Total Sum of Allowed this month"
    type: percent_of_total
    hidden: yes
    sql: ${sum_allowed_this_month} ;;
  }

  measure: 3_month_projected_allowed {
    label: "3 Month Projected Allowed"
    type: average
    value_format: "$#,###"
    sql: ${TABLE}.adjusted_allowed ;;
  }

  measure: Total_Payer_Paid {
    type: sum
    hidden: yes
    sql: ${payer_paid} ;;
  }

  measure: Total_Customer_Inv_Amt {
    type: sum
    hidden: yes
    sql: ${Customer_Inv_Amt} ;;
  }

  measure: Total_Physician_Inv_Amt {
    type: sum
    hidden: yes
    sql: ${Physician_Inv_Amt} ;;
  }

  measure: Total_Transfer_Amt {
    type: sum
    hidden: yes
    sql: ${Transfer_Amt} ;;
  }

  measure: Total_Comp_Amt {
    type: sum
    hidden: yes
    sql: ${Comp_Amt} ;;
  }

  measure: Total_ASP {
    type: sum
    hidden: yes
    sql: ${TABLE}.ASP ;;
  }

  measure: Percent_Payer_Paid {
    label: "Payer % Contribution to Average Allowed (no projections)"
    type: number
    hidden: yes
    value_format: "#.##\%"
    sql: 100.00 * coalesce(${Total_Payer_Paid}/nullif(${Total_ASP},0),0) ;;
  }

  measure: Percent_Customer_Inv_Amt {
    label: "Customer % Contribution to Average Allowed (no projections)"
    type: number
    hidden: yes
    value_format: "#.##\%"
    sql: 100.00 * coalesce(${Total_Customer_Inv_Amt}/nullif(${Total_ASP},0),0) ;;
  }

  measure: Percent_Physician_Inv_Amt {
    label: "Physician % Contribution to Average Allowed (no projections)"
    type: number
    hidden: yes
    value_format: "#.##\%"
    sql: 100.00 * coalesce(${Total_Physician_Inv_Amt}/nullif(${Total_ASP},0),0) ;;
  }

  measure: Percent_Transfer_Amt {
    label: "Transfer % Contribution to Average Allowed (no projections)"
    type: number
    hidden: yes
    value_format: "#.##\%"
    sql: -100.00 * coalesce(${Total_Transfer_Amt}/nullif(${Total_ASP},0),0) ;;
  }

  measure: Percent_Comp_Amt {
    label: "Comp % Contribution to Average Allowed (no projections)"
    type: number
    hidden: yes
    value_format: "#.##\%"
    sql: -100.00 * coalesce(${Total_Comp_Amt}/nullif(${Total_ASP},0),0) ;;
  }

  measure: Contribution_Payer_Paid {
    label: "Payer Paid $ Contribution to Average Allowed (no projections)"
    type: number
    value_format: "$#,###"
    sql: ${Percent_Payer_Paid}*${average_actual_asp}/100.00 ;;
  }

  measure: Contribution_Customer_Inv_Amt {
    label: "Customer $ Contribution to Average Allowed (no projections)"
    type: number
    value_format: "$#,###"
    sql: ${Percent_Customer_Inv_Amt}*${average_actual_asp}/100.00 ;;
  }

  measure: Contribution_Physician_Inv_Amt {
    label: "Physician $ Contribution to Average Allowed (no projections)"
    type: number
    value_format: "$#,###"
    sql: ${Percent_Physician_Inv_Amt}*${average_actual_asp}/100.00 ;;
  }

  measure: Contribution_Transfer_Amt {
    label: "Transfer $ Contribution to Average Allowed (no projections)"
    type: number
    value_format: "$#,###"
    sql: ${Percent_Transfer_Amt}*${average_actual_asp}/100.00 ;;
  }

  measure: Contribution_Comp_Amt {
    label: "Comp $ Contribution to Average Allowed (no projections)"
    type: number
    value_format: "$#,###"
    sql: ${Percent_Comp_Amt}*${average_actual_asp}/100.00 ;;
  }

  measure: average_actual_asp {
    label: "Average Allowed (no projections)"
    type: average
    value_format: "$##0"
    sql: ${TABLE}.adjusted_allowed_term_only ;;
  }

  measure: average_projected_asp {
    label: "Average Allowed"
    type: average
    value_format: "$##0"
    drill_fields: [detail2*]
    sql: ${TABLE}.adjusted_allowed ;;
  }

  measure: projected_asp_growth_over_time {
    type: percent_of_previous
    hidden: yes
    sql: ${average_projected_asp} ;;
  }

  measure: percent_of_contribution_to_allowed {
    type: percent_of_total
    sql: ${sum_allowed} ;;
  }

  measure: asp_fps_1 {
    type: average
    hidden: yes
    sql: ${adjusted_asp} ;;
  }

  measure: asp_fps_2 {
    type: average
    hidden: yes
    sql: ${adjusted_asp} ;;
  }

  measure: asp_ips {
    type: average
    hidden: yes
    sql: ${adjusted_asp} ;;
  }

  measure: asp_ics {
    type: average
    hidden: yes
    sql: ${adjusted_asp} ;;
  }

  measure: average_projected_asp_last_week {
    type: average
    hidden: yes
    filters: [order.completed_week: "last week"]
    sql: ${adjusted_asp} ;;
  }

  measure: average_projected_asp_WoW {
    label: "Average Allowed W/W"
    type: number
    value_format: "#.00%"
    sql: ((${average_projected_asp_this_week}-${average_projected_asp_last_week})/nullif(${average_projected_asp_last_week},0)) ;;
  }

  measure: average_projected_asp_this_week {
    type: average
    hidden: yes
    filters: [order.completed_week: "this week"]
    sql: ${adjusted_asp} ;;
  }

  measure: average_projected_asp_two_months_ago {
    type: average
    hidden: yes
    filters: [order.completed_month: "2 months ago"]
    sql: ${adjusted_asp} ;;
  }

  measure: average_projected_asp_last_month {
    type: average
    hidden: yes
    filters: [order.completed_month: "last month"]
    sql: ${adjusted_asp} ;;
  }

  measure: average_projected_asp_MoM {
    label: "Average Allowed M/M"
    type: number
    value_format: "#.00\%"
    sql: ((${average_projected_asp_this_month}-${average_projected_asp_last_month})*100/nullif(${average_projected_asp_last_month},0)) ;;
  }

  measure: average_projected_asp_previous_MoM {
    label: "Previous Average Allowed M/M"
    type: number
    value_format: "#.00\%"
    sql: ((${average_projected_asp_last_month}-${average_projected_asp_two_months_ago})*100/nullif(${average_projected_asp_two_months_ago},0)) ;;
  }

  measure: average_projected_asp_MoM_difference {
    label: "Average Allowed M/M Difference"
    type: number
    value_format: "$#.00"
    sql: (${average_projected_asp_this_month}-${average_projected_asp_last_month}) ;;
  }

  measure: average_projected_asp_this_month {
    label: "Average Allowed This Month"
    type: average
    value_format: "$#,###"
    filters: [order.completed_month: "this month"]
    sql: ${adjusted_asp} ;;
  }

  measure: average_actual_asp_this_month {
    label: "Average Allowed (no projections) This Month"
    type: average
    value_format: "$##0"
    filters: [order.completed_month: "this month"]
    sql: ${TABLE}.adjusted_allowed_term_only ;;
  }

  measure: average_projected_asp_last_quarter {
    type: average
    sql: CASE WHEN ${order.is_last_quarter} = 'Yes' THEN ${adjusted_asp} ELSE NULL END ;;
  }

  measure: average_projected_asp_this_quarter {
    label: "Average Allowed This Quarter"
    type: average
    value_format: "$##0"
    sql: CASE WHEN ${order.is_this_quarter} = 'Yes' THEN ${adjusted_asp} ELSE NULL END ;;
  }

  measure: average_projected_asp_QoQ {
    label: "Average Allowed Q/Q"
    type: number
    value_format: "#.00%"
    sql: ((${average_projected_asp_this_quarter}-${average_projected_asp_last_quarter})/nullif(${average_projected_asp_last_quarter},0)) ;;
  }
}

final_allowed.view.lookml · 320 lines

source-preserved

- view: final_allowed
  derived_table:
    sql: SELECT
  o.order_id AS order_id
  , a.ASP AS actual_asp
  , p.projected_insurance_asp_for_join as projected_insurance_asp
  , o.bill_type as bill_type
  , terminal_state.terminal_bool as terminal_bool
  , a.completed_on
  , o.status_name
  , concat(o.product,o.methodology) as product_method
  ,a.Comp_Amt
  ,a.Customer_Inv_Amt
  ,a.Physician_Inv_Amt
  ,a.payer_paid
  ,a.Transfer_Amt
  ,a.ASP
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN terminal_state.terminal_bool = FALSE THEN
      CASE WHEN o.status_name = 'Benefits Researched' THEN
        CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
          WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120
          ELSE p.projected_insurance_asp_for_join
          END
        ELSE p.projected_insurance_asp_for_join
        END
      ELSE a.ASP
      END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    ELSE a.ASP
    END AS adjusted_allowed
  ,CASE WHEN o.bill_type = 'in' THEN
    CASE WHEN o.status_name = 'Benefits Researched' THEN
       CASE WHEN concat(o.product,o.methodology) = '<% parameter product_method_1 %>' THEN 99
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_2 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_3 %>' THEN 299
        WHEN concat(o.product,o.methodology) = '<% parameter product_method_4 %>' THEN 120 ELSE NULL END
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) > 0 THEN NULL
    WHEN o.bill_type = <% parameter bill_type_cnsmt %> and position('HealthCompany' in o.account_identifier) = 0 THEN o.price
    WHEN o.bill_type = <% parameter bill_type_cc_placeholder %> THEN a.ASP
    WHEN terminal_bool = TRUE THEN a.ASP ELSE NULL END as adjusted_allowed_term_only
FROM (
  SELECT
    "order".id AS order_id
    ,"order".product AS product
    ,"order".disease_panel AS disease_panel
    ,"order".testing_methodology AS methodology
    ,"order".bill_type as bill_type
    ,"order".account_identifier as account_identifier
    ,"order".price as price
    ,TO_CHAR("order".completed_on, 'YYYY-MM') AS completed_month
    ,insuranceclaim.payer_identifier as payer_identifier
    ,insuranceclaim.plan_name as plan_name
    ,insuranceclaim.status_name as status_name
  FROM  ${SCHEMA}.order
  LEFT JOIN  ${SCHEMA}.insuranceclaim AS insuranceclaim ON insuranceclaim.order_id = "order".id
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11) AS o
LEFT JOIN ${allowed_by_order.SQL_TABLE_NAME} AS a ON a.id = o.order_id
LEFT JOIN ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id = o.order_id
LEFT JOIN ${projection.SQL_TABLE_NAME} AS p ON p.product = o.product
  AND o.methodology = p.methodology
  AND o.disease_panel = p.disease_panel
  AND coalesce(o.payer_identifier,'blank') = coalesce(p.payer_identifier,'blank')
  AND coalesce(o.plan_name,'blank') = coalesce(p.plan_name,'blank')
  AND o.completed_month = p.completed_month
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
  fields:
  - dimension: order_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: bill_type
    hidden: true
    sql: ${TABLE}.bill_type
  - dimension: actual_asp
    hidden: true
    sql: ${TABLE}.actual_asp
  - dimension: projected_asp
    hidden: true
    sql: ${TABLE}.projected_insurance_asp
  - dimension: terminal_bool
    hidden: true
    sql: ${TABLE}.terminal_bool
  - dimension: payer_paid
    type: number
    hidden: true
    sql: ${TABLE}.payer_paid
  - dimension: Customer_Inv_Amt
    type: number
    hidden: true
    sql: ${TABLE}.Customer_Inv_Amt
  - dimension: Physician_Inv_Amt
    type: number
    hidden: true
    sql: ${TABLE}.Physician_Inv_Amt
  - dimension: Transfer_Amt
    type: number
    hidden: true
    sql: ${TABLE}.Transfer_Amt
  - dimension: Comp_Amt
    type: number
    hidden: true
    sql: ${TABLE}.Comp_Amt
  - dimension: unadjusted_asp
    type: number
    hidden: true
    sql: ${TABLE}.adjusted_allowed_term_only
  - dimension: adjusted_asp
    type: number
    hidden: true
    sql: CASE WHEN ${bill_type} = 'in' THEN
  CASE WHEN ${terminal_bool} = FALSE THEN
    CASE WHEN ${claim.status_name} = 'Benefits Researched' THEN
      CASE WHEN product_method = '<% parameter product_method_1 %>' THEN 99
        WHEN product_method = '<% parameter product_method_2 %>' THEN 299
        WHEN product_method = '<% parameter product_method_3 %>' THEN 299
        WHEN product_method = '<% parameter product_method_4 %>' THEN ${projected_asp}
        ELSE ${projected_asp}
        END
      ELSE ${projected_asp}
      END
    ELSE ${unadjusted_asp}
    END
    WHEN ${bill_type} = <% parameter bill_type_cnsmt %> THEN ${unadjusted_asp}
    WHEN ${bill_type} = <% parameter bill_type_cc_placeholder %> THEN ${unadjusted_asp}
  ELSE ${unadjusted_asp}
  END
  - measure: sum_allowed
    type: sum
    label: Sum of Allowed
    value_format: '$#,###'
    sql: ${adjusted_asp}
  - measure: sum_allowed_this_month
    type: sum
    label: Sum of Allowed this month
    value_format: '$#,###'
    sql: ${adjusted_asp}
  - measure: percent_of_sum_allowed
    type: percent_of_total
    label: Percent of Total Sum of Allowed
    sql: ${sum_allowed}
  - measure: percent_of_sum_allowed_this_month
    type: percent_of_total
    label: Percent of Total Sum of Allowed this month
    sql: ${sum_allowed_this_month}
  - measure: 3_month_projected_allowed
    type: average
    label: 3 Month Projected Allowed
    value_format: '$#,###'
    sql: ${TABLE}.adjusted_allowed
  - measure: Total_Payer_Paid
    type: sum
    sql: ${payer_paid}
  - measure: Total_Customer_Inv_Amt
    type: sum
    sql: ${Customer_Inv_Amt}
  - measure: Total_Physician_Inv_Amt
    type: sum
    sql: ${Physician_Inv_Amt}
  - measure: Total_Transfer_Amt
    type: sum
    sql: ${Transfer_Amt}
  - measure: Total_Comp_Amt
    type: sum
    sql: ${Comp_Amt}
  - measure: Total_ASP
    type: sum
    sql: ${TABLE}.ASP
  - measure: Percent_Payer_Paid
    type: number
    label: 'Payer % Contribution to Average Allowed (no projections)'
    value_format: '#.##\%'
    sql: 100.00 * coalesce(${Total_Payer_Paid}/nullif(${Total_ASP},0),0)
  - measure: Percent_Customer_Inv_Amt
    type: number
    label: 'Customer % Contribution to Average Allowed (no projections)'
    value_format: '#.##\%'
    sql: 100.00 * coalesce(${Total_Customer_Inv_Amt}/nullif(${Total_ASP},0),0)
  - measure: Percent_Physician_Inv_Amt
    type: number
    label: 'Physician % Contribution to Average Allowed (no projections)'
    value_format: '#.##\%'
    sql: 100.00 * coalesce(${Total_Physician_Inv_Amt}/nullif(${Total_ASP},0),0)
  - measure: Percent_Transfer_Amt
    type: number
    label: 'Transfer % Contribution to Average Allowed (no projections)'
    value_format: '#.##\%'
    sql: -100.00 * coalesce(${Total_Transfer_Amt}/nullif(${Total_ASP},0),0)
  - measure: Percent_Comp_Amt
    type: number
    label: 'Comp % Contribution to Average Allowed (no projections)'
    value_format: '#.##\%'
    sql: -100.00 * coalesce(${Total_Comp_Amt}/nullif(${Total_ASP},0),0)
  - measure: Contribution_Payer_Paid
    type: number
    label: Payer Paid $ Contribution to Average Allowed (no projections)
    value_format: '$#,###'
    sql: ${Percent_Payer_Paid}*${average_actual_asp}/100.00
  - measure: Contribution_Customer_Inv_Amt
    type: number
    label: Customer $ Contribution to Average Allowed (no projections)
    value_format: '$#,###'
    sql: ${Percent_Customer_Inv_Amt}*${average_actual_asp}/100.00
  - measure: Contribution_Physician_Inv_Amt
    type: number
    label: Physician $ Contribution to Average Allowed (no projections)
    value_format: '$#,###'
    sql: ${Percent_Physician_Inv_Amt}*${average_actual_asp}/100.00
  - measure: Contribution_Transfer_Amt
    type: number
    label: Transfer $ Contribution to Average Allowed (no projections)
    value_format: '$#,###'
    sql: ${Percent_Transfer_Amt}*${average_actual_asp}/100.00
  - measure: Contribution_Comp_Amt
    type: number
    label: Comp $ Contribution to Average Allowed (no projections)
    value_format: '$#,###'
    sql: ${Percent_Comp_Amt}*${average_actual_asp}/100.00
  - measure: average_actual_asp
    type: average
    label: Average Allowed (no projections)
    value_format: '$##0'
    sql: ${TABLE}.adjusted_allowed_term_only
  - measure: average_projected_asp
    type: average
    label: Average Allowed
    value_format: '$##0'
    drill_fields:
    - detail2*
    sql: ${TABLE}.adjusted_allowed
  - measure: projected_asp_growth_over_time
    type: percent_of_previous
    sql: ${average_projected_asp}
  - measure: percent_of_contribution_to_allowed
    type: percent_of_total
    sql: ${sum_allowed}
  - measure: asp_fps_1
    type: average
    sql: ${adjusted_asp}
  - measure: asp_fps_2
    type: average
    sql: ${adjusted_asp}
  - measure: asp_ips
    type: average
    sql: ${adjusted_asp}
  - measure: asp_ics
    type: average
    sql: ${adjusted_asp}
  - measure: average_projected_asp_last_week
    type: average
    filters:
    - order.completed_week: last week
    sql: ${adjusted_asp}
  - measure: average_projected_asp_WoW
    type: number
    label: Average Allowed W/W
    value_format: '#.00%'
    sql: ((${average_projected_asp_this_week}-${average_projected_asp_last_week})/nullif(${average_projected_asp_last_week},0))
  - measure: average_projected_asp_this_week
    type: average
    filters:
    - order.completed_week: this week
    sql: ${adjusted_asp}
  - measure: average_projected_asp_two_months_ago
    type: average
    filters:
    - order.completed_month: 2 months ago
    sql: ${adjusted_asp}
  - measure: average_projected_asp_last_month
    type: average
    filters:
    - order.completed_month: last month
    sql: ${adjusted_asp}
  - measure: average_projected_asp_MoM
    type: number
    label: Average Allowed M/M
    value_format: '#.00\%'
    sql: ((${average_projected_asp_this_month}-${average_projected_asp_last_month})*100/nullif(${average_projected_asp_last_month},0))
  - measure: average_projected_asp_previous_MoM
    type: number
    label: Previous Average Allowed M/M
    value_format: '#.00\%'
    sql: ((${average_projected_asp_last_month}-${average_projected_asp_two_months_ago})*100/nullif(${average_projected_asp_two_months_ago},0))
  - measure: average_projected_asp_MoM_difference
    type: number
    label: Average Allowed M/M Difference
    value_format: '$#.00'
    sql: (${average_projected_asp_this_month}-${average_projected_asp_last_month})
  - measure: average_projected_asp_this_month
    type: average
    label: Average Allowed This Month
    value_format: '$#,###'
    filters:
    - order.completed_month: this month
    sql: ${adjusted_asp}
  - measure: average_actual_asp_this_month
    type: average
    label: Average Allowed (no projections) This Month
    value_format: '$##0'
    filters:
    - order.completed_month: this month
    sql: ${TABLE}.adjusted_allowed_term_only
  - measure: average_projected_asp_last_quarter
    type: average
    sql: CASE WHEN ${order.is_last_quarter} = 'Yes' THEN ${adjusted_asp} ELSE NULL END
  - measure: average_projected_asp_this_quarter
    type: average
    label: Average Allowed This Quarter
    value_format: '$##0'
    sql: CASE WHEN ${order.is_this_quarter} = 'Yes' THEN ${adjusted_asp} ELSE NULL END
  - measure: average_projected_asp_QoQ
    type: number
    label: Average Allowed Q/Q
    value_format: '#.00%'
    sql: ((${average_projected_asp_this_quarter}-${average_projected_asp_last_quarter})/nullif(${average_projected_asp_last_quarter},0))

final_allowed.sql
final_allowed.sql
final_allowed.view.lkml
final_allowed.view.lookml

Final Allowed

Blocks

Assumptions

Contents