A Assignment

view: a_assignment {
  sql_table_name: agg.a_assignment ;;

  dimension: agency {
    type: int
    sql: ${TABLE}.agency ;;
  }

  dimension: agency_team {
    type: int
    sql: ${TABLE}.agency_team ;;
  }

  dimension: assignment {
    type: int
    sql: ${TABLE}.assignment ;;
  }

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

  dimension: contractor {
    type: int
    sql: ${TABLE}.contractor ;;
  }

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

  dimension: employer {
    type: int
    sql: ${TABLE}.employer ;;
  }

  dimension: employer_team {
    type: int
    sql: ${TABLE}.employer_team ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: include_in_generic_stats {
    type: yesno
    sql: ${TABLE}.include_in_generic_stats ;;
  }

  dimension: isfp {
    type: int
    sql: ${TABLE}.isfp ;;
  }

  dimension: ishr {
    type: int
    sql: ${TABLE}.ishr ;;
  }

  dimension: isw2 {
    type: yesno
    sql: ${TABLE}.isw2 ;;
  }

  dimension: category_id {
    type: int
    sql: ${TABLE}.job_category ;;
  }

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

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

  dimension: off_the_network {
    type: yesno
    sql: ${TABLE}.off_the_network ;;
  }

  dimension: opening {
    type: int
    sql: ${TABLE}.opening ;;
  }

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

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

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

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

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

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

  dimension: is_marketplace {
    label: "Marketplace (Y/N)"
    type: yesno
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') ;;
  }

  dimension: is_marketplace_core {
    label: "Marketplace - Core (Y/N)"
    type: yesno
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND NOT ${is_gdev} AND ${sparseattributes.referencedfk} IS NULL AND ${opportunities.client_segment} IS NULL ;;
  }

  dimension: is_marketplace_smb {
    label: "Marketplace - SMB (Y/N)"
    type: yesno
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND ${opportunities.client_segment} = 'SMB' ;;
  }

  dimension: is_marketplace_enterprise {
    label: "Marketplace - Enterprise (Y/N)"
    type: yesno
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND (${opportunities.client_segment} = 'Enterprise' OR ${is_gdev}) ;;
  }

  dimension: is_managed_services {
    label: "Managed Services (Y/N)"
    type: yesno
    sql: ${dept} NOT IN ('BILL','MANAGED_CONTRACTOR') AND ${sparseattributes.value} = 'MANAGED_SERVICES' ;;
  }

  dimension: is_gdev {
    label: "GDev (Y/N)"
    type: yesno
    sql: ${employer} IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>) ;;
  }

  dimension: is_internal {
    label: "Internal (Y/N)"
    type: yesno
    sql: (${dept} <> 'BILL' AND ${sparseattributes.referencedfk} IS NULL) OR (${dept} = 'BILL' AND NOT(${include_in_generic_stats})) ;;
  }

  dimension: gsv_logic {
    label: "GSV (Y/N)"
    type: yesno
    sql: (${is_managed_services} OR ${is_marketplace}) ;;
  }

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

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

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

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

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

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

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

  measure: sum_total_charge_all {
    label: "Total Charge (All)"
    type: sum
    sql: ${total_charge} ;;
  }

  measure: sum_gsv {
    label: "Total GSV"
    type: sum
    filters: [gsv_logic: "True"]
    sql: ${total_charge} ;;
  }

  measure: count {
    type: count
  }

  measure: sum_gsv_cum {
    label: "Total GSV (Cumulative)"
    type: running_total
    sql: ${sum_gsv} ;;
  }

  measure: count_marketplace_assignment {
    type: count
    filters: [is_marketplace: "True"]
  }

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

  measure: sum_gsv_mkt {
    label: "GSV - Marketplace"
    type: sum
    filters: [is_marketplace: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_gsv_mkt_pct_previous {
    label: "Percent of Previous (GSV - Mkt)"
    type: percent_of_previous
    sql: ${sum_gsv_mkt} ;;
  }

  measure: sum_gsv_mkt_core {
    label: "GSV - Marketplace - Core"
    type: sum
    filters: [is_marketplace_core: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_gsv_mkt_smb {
    label: "GSV - Marketplace - SMB"
    type: sum
    filters: [is_marketplace_smb: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_gsv_mkt_enterprise {
    label: "GSV - Marketplace - Ent"
    type: sum
    filters: [is_marketplace_enterprise: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_gsv_managed_services {
    label: "GSV - Managed Services/PTC"
    type: sum
    filters: [is_managed_services: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_total_charge_internal {
    label: "Total Charge (Internal)"
    type: sum
    filters: [is_internal: "True"]
    sql: ${total_charge} ;;
  }

  measure: sum_total_charge_mngd_cos {
    label: "Total Charge (MCoS)"
    type: sum
    filters: [dept: "MANAGED_CONTRACTOR"]
    filters: [sparseattributes.value: "MANAGED_SERVICES"]
    sql: ${total_charge} ;;
  }

  measure: sum_hours_all {
    label: "Total Hours (All)"
    type: sum
    sql: ${hours} ;;
  }

  measure: sum_hours {
    label: "Total Hours Billed"
    type: sum
    filters: [gsv_logic: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_cum {
    label: "Total Hours Billed (Cumulative)"
    type: running_total
    filters: [gsv_logic: "True"]
    sql: ${sum_hours} ;;
  }

  measure: sum_hours_mkt {
    label: "Hours Billed - Marketplace"
    type: sum
    filters: [is_marketplace: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_mkt_pct_previous {
    type: percent_of_previous
    sql: ${sum_hours_mkt} ;;
  }

  measure: sum_hours_mkt_core {
    label: "Hours Billed - Marketplace - Core"
    type: sum
    filters: [is_marketplace_core: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_mkt_smb {
    label: "Hours Billed - Marketplace - SMB"
    type: sum
    filters: [is_marketplace_smb: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_mkt_enterprise {
    label: "Hours Billed - Marketplace - Ent"
    type: sum
    filters: [is_marketplace_enterprise: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_managed_services {
    label: "Hours Billed - Managed Services/PTC"
    type: sum
    filters: [is_managed_services: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_internal {
    label: "Total Hours (Internal)"
    type: sum
    filters: [is_internal: "True"]
    sql: ${hours} ;;
  }

  measure: sum_hours_mngd_cos {
    label: "Total Hours (MCoS)"
    type: sum
    filters: [dept: "MANAGED_CONTRACTOR"]
    filters: [sparseattributes.value: "MANAGED_SERVICES"]
    sql: ${hours} ;;
  }
}

- view: a_assignment
  sql_table_name: agg.a_assignment
  fields:
  - dimension: agency
    type: int
    sql: ${TABLE}.agency
  - dimension: agency_team
    type: int
    sql: ${TABLE}.agency_team
  - dimension: assignment
    type: int
    sql: ${TABLE}.assignment
  - dimension: bonus
    type: number
    sql: ${TABLE}.bonus
  - dimension: contractor
    type: int
    sql: ${TABLE}.contractor
  - dimension: dept
    sql: ${TABLE}.dept
  - dimension: employer
    type: int
    sql: ${TABLE}.employer
  - dimension: employer_team
    type: int
    sql: ${TABLE}.employer_team
  - dimension: fp_charge
    type: number
    sql: ${TABLE}.fp_charge
  - dimension: hours
    type: number
    sql: ${TABLE}.hours
  - dimension: hours_90_not_offnetwork
    type: number
    sql: ${TABLE}.hours_90_not_offnetwork
  - dimension: hours_not_offnetwork
    type: number
    sql: ${TABLE}.hours_not_offnetwork
  - dimension: hours_offline
    type: number
    sql: ${TABLE}.hours_offline
  - dimension: hours_offline_7
    type: number
    sql: ${TABLE}.hours_offline_7
  - dimension: hours_online_7
    type: number
    sql: ${TABLE}.hours_online_7
  - dimension: hours_total_180
    type: number
    sql: ${TABLE}.hours_total_180
  - dimension: hours_total_30
    type: number
    sql: ${TABLE}.hours_total_30
  - dimension: hours_total_7
    type: number
    sql: ${TABLE}.hours_total_7
  - dimension: hours_total_7_stats
    type: number
    sql: ${TABLE}.hours_total_7_stats
  - dimension: hours_total_90
    type: number
    sql: ${TABLE}.hours_total_90
  - dimension: hours_total_90_stats
    type: number
    sql: ${TABLE}.hours_total_90_stats
  - dimension: hr_charge
    type: number
    sql: ${TABLE}.hr_charge
  - dimension: hr_charge_30
    type: number
    sql: ${TABLE}.hr_charge_30
  - dimension: hr_rate
    type: number
    sql: ${TABLE}.hr_rate
  - dimension: hr_trx
    type: number
    sql: ${TABLE}.hr_trx
  - dimension: include_in_generic_stats
    type: yesno
    sql: ${TABLE}.include_in_generic_stats
  - dimension: isfp
    type: int
    sql: ${TABLE}.isfp
  - dimension: ishr
    type: int
    sql: ${TABLE}.ishr
  - dimension: isw2
    type: yesno
    sql: ${TABLE}.isw2
  - dimension: category_id
    type: int
    sql: ${TABLE}.job_category
  - dimension: longest_hourly_14
    type: number
    sql: ${TABLE}.longest_hourly_14
  - dimension: team_user_id
    sql: ${TABLE}.team_user_id
  - dimension: off_the_network
    type: yesno
    sql: ${TABLE}.off_the_network
  - dimension: opening
    type: int
    sql: ${TABLE}.opening
  - dimension: refund
    type: number
    sql: ${TABLE}.refund
  - dimension: revenue
    type: number
    sql: ${TABLE}.revenue
  - dimension: salary
    type: number
    sql: ${TABLE}.salary
  - dimension: salary_30
    type: number
    sql: ${TABLE}.salary_30
  - dimension: total_charge
    type: number
    sql: ${TABLE}.total_charge
  - dimension: weekly_stipend_cost
    type: number
    sql: ${TABLE}.weekly_stipend_cost
  - dimension: is_marketplace
    type: yesno
    label: Marketplace (Y/N)
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true')
  - dimension: is_marketplace_core
    type: yesno
    label: Marketplace - Core (Y/N)
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND NOT ${is_gdev} AND ${sparseattributes.referencedfk} IS NULL AND ${opportunities.client_segment} IS NULL
  - dimension: is_marketplace_smb
    type: yesno
    label: Marketplace - SMB (Y/N)
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND ${opportunities.client_segment} = 'SMB'
  - dimension: is_marketplace_enterprise
    type: yesno
    label: Marketplace - Enterprise (Y/N)
    sql: (${dept} = 'BILL' AND ${include_in_generic_stats} = 'true') AND (${opportunities.client_segment} = 'Enterprise' OR ${is_gdev})
  - dimension: is_managed_services
    type: yesno
    label: Managed Services (Y/N)
    sql: ${dept} NOT IN ('BILL','MANAGED_CONTRACTOR') AND ${sparseattributes.value} = 'MANAGED_SERVICES'
  - dimension: is_gdev
    type: yesno
    label: GDev (Y/N)
    sql: ${employer} IN (<% parameter employer_id_1 %>,<% parameter employer_id_2 %>)
  - dimension: is_internal
    type: yesno
    label: Internal (Y/N)
    sql: (${dept} <> 'BILL' AND ${sparseattributes.referencedfk} IS NULL) OR (${dept} = 'BILL' AND NOT(${include_in_generic_stats}))
  - dimension: gsv_logic
    type: yesno
    label: GSV (Y/N)
    sql: (${is_managed_services} OR ${is_marketplace})
  - dimension_group: date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    - month_num
    sql: ${TABLE}.date
  - dimension_group: first_fp_date_worked
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.first_fp_date_worked
  - dimension_group: first_hr_date_worked
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.first_hr_date_worked
  - dimension_group: first_hr_datetime_worked
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_hr_datetime_worked
  - dimension_group: last_fp_date_worked
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.last_fp_date_worked
  - dimension_group: last_hr_date_worked
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.last_hr_date_worked
  - dimension_group: last_hr_date_worked_no_check
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.last_hr_date_worked_no_check
  - measure: sum_total_charge_all
    type: sum
    label: Total Charge (All)
    sql: ${total_charge}
  - measure: sum_gsv
    type: sum
    label: Total GSV
    filters:
    - gsv_logic: True
    sql: ${total_charge}
  - measure: count
    type: count
  - measure: sum_gsv_cum
    type: running_total
    label: Total GSV (Cumulative)
    sql: ${sum_gsv}
  - measure: count_marketplace_assignment
    type: count
    filters:
    - is_marketplace: True
  - measure: percent_marketplace_assignment
    type: number
    sql: 100.0 * ${count_marketplace_assignment}/ NULLIF(${count},0)
  - measure: sum_gsv_mkt
    type: sum
    label: GSV - Marketplace
    filters:
    - is_marketplace: True
    sql: ${total_charge}
  - measure: sum_gsv_mkt_pct_previous
    type: percent_of_previous
    label: Percent of Previous (GSV - Mkt)
    sql: ${sum_gsv_mkt}
  - measure: sum_gsv_mkt_core
    type: sum
    label: GSV - Marketplace - Core
    filters:
    - is_marketplace_core: True
    sql: ${total_charge}
  - measure: sum_gsv_mkt_smb
    type: sum
    label: GSV - Marketplace - SMB
    filters:
    - is_marketplace_smb: True
    sql: ${total_charge}
  - measure: sum_gsv_mkt_enterprise
    type: sum
    label: GSV - Marketplace - Ent
    filters:
    - is_marketplace_enterprise: True
    sql: ${total_charge}
  - measure: sum_gsv_managed_services
    type: sum
    label: GSV - Managed Services/PTC
    filters:
    - is_managed_services: True
    sql: ${total_charge}
  - measure: sum_total_charge_internal
    type: sum
    label: Total Charge (Internal)
    filters:
    - is_internal: True
    sql: ${total_charge}
  - measure: sum_total_charge_mngd_cos
    type: sum
    label: Total Charge (MCoS)
    filters:
    - dept: MANAGED_CONTRACTOR
    - sparseattributes.value: MANAGED_SERVICES
    sql: ${total_charge}
  - measure: sum_hours_all
    type: sum
    label: Total Hours (All)
    sql: ${hours}
  - measure: sum_hours
    type: sum
    label: Total Hours Billed
    filters:
    - gsv_logic: True
    sql: ${hours}
  - measure: sum_hours_cum
    type: running_total
    label: Total Hours Billed (Cumulative)
    filters:
    - gsv_logic: True
    sql: ${sum_hours}
  - measure: sum_hours_mkt
    type: sum
    label: Hours Billed - Marketplace
    filters:
    - is_marketplace: True
    sql: ${hours}
  - measure: sum_hours_mkt_pct_previous
    type: percent_of_previous
    sql: ${sum_hours_mkt}
  - measure: sum_hours_mkt_core
    type: sum
    label: Hours Billed - Marketplace - Core
    filters:
    - is_marketplace_core: True
    sql: ${hours}
  - measure: sum_hours_mkt_smb
    type: sum
    label: Hours Billed - Marketplace - SMB
    filters:
    - is_marketplace_smb: True
    sql: ${hours}
  - measure: sum_hours_mkt_enterprise
    type: sum
    label: Hours Billed - Marketplace - Ent
    filters:
    - is_marketplace_enterprise: True
    sql: ${hours}
  - measure: sum_hours_managed_services
    type: sum
    label: Hours Billed - Managed Services/PTC
    filters:
    - is_managed_services: True
    sql: ${hours}
  - measure: sum_hours_internal
    type: sum
    label: Total Hours (Internal)
    filters:
    - is_internal: True
    sql: ${hours}
  - measure: sum_hours_mngd_cos
    type: sum
    label: Total Hours (MCoS)
    filters:
    - dept: MANAGED_CONTRACTOR
    - sparseattributes.value: MANAGED_SERVICES
    sql: ${hours}