data.ml
LKML Call center

Labor Hour

Labor Hour model for call center.

inferred

Blocks

labor_hour.view.lkml · 97 lines
inferred 
view: labor_hour {
  sql_table_name: LaborHourStat ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.laborhourID ;;
  }

  dimension: campaign_id {
    type: int
    hidden: yes
    sql: ${TABLE}.callCampaignID ;;
  }

  dimension: user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.callUserID ;;
  }

  dimension: campaign_available_sec {
    type: int
    sql: ${TABLE}.campaignAvailableSeconds ;;
  }

  dimension: campaign_available_hrs {
    type: number
    sql: ${TABLE}.campaignAvailableSeconds / 3600.00 ;;
  }

  dimension: campaign_calls {
    type: int
    sql: ${TABLE}.campaignCalls ;;
  }

  dimension: campaign_labor_hrs {
    type: number
    sql: ${TABLE}.campaignLH ;;
  }

  dimension: campaign_talk_sec {
    type: int
    sql: ${TABLE}.campaignTalkSeconds ;;
  }

  dimension: campaign_talk_hrs {
    type: number
    sql: ${TABLE}.campaignTalkSeconds / 3600.00 ;;
  }

  dimension: utilization_tier {
    type: tier
    sql: ${campaign_talk_hrs} / NULLIF(${campaign_talk_hrs} + ${campaign_available_hrs}, 0) * 100.0 ;;
  }

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

  dimension_group: created_time {
    type: date_time
    sql: ${TABLE}.laborhourCreatedOn ;;
  }

  dimension_group: updated_time {
    type: date_time
    sql: ${TABLE}.laborhourUpdatedOn ;;
  }

  measure: total_campaign_available_hrs {
    type: sum
    sql: ${campaign_available_hrs} ;;
  }

  measure: total_campaign_calls {
    type: sum
    sql: ${campaign_calls} ;;
  }

  measure: total_campaign_labor_hrs {
    type: sum
    sql: ${campaign_labor_hrs} ;;
  }

  measure: total_campaign_talk_hrs {
    type: sum
    sql: ${campaign_talk_hrs} ;;
  }

  measure: utilization {
    type: number
    sql: NULLIF(${total_campaign_talk_hrs} + ${total_campaign_available_hrs}, 0) / NULLIF(${total_campaign_labor_hrs}, 0) * 100.0 ;;
  }
}
labor_hour.view.lookml · 64 lines
source-preserved 
- view: labor_hour
  sql_table_name: LaborHourStat
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.laborhourID
  - dimension: campaign_id
    type: int
    hidden: true
    sql: ${TABLE}.callCampaignID
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.callUserID
  - dimension: campaign_available_sec
    type: int
    sql: ${TABLE}.campaignAvailableSeconds
  - dimension: campaign_available_hrs
    type: number
    sql: ${TABLE}.campaignAvailableSeconds / 3600.00
  - dimension: campaign_calls
    type: int
    sql: ${TABLE}.campaignCalls
  - dimension: campaign_labor_hrs
    type: number
    sql: ${TABLE}.campaignLH
  - dimension: campaign_talk_sec
    type: int
    sql: ${TABLE}.campaignTalkSeconds
  - dimension: campaign_talk_hrs
    type: number
    sql: ${TABLE}.campaignTalkSeconds / 3600.00
  - dimension: utilization_tier
    type: tier
    sql: ${campaign_talk_hrs} / NULLIF(${campaign_talk_hrs} + ${campaign_available_hrs}, 0) * 100.0
  - dimension_group: call
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.callDate
  - dimension_group: created_time
    type: date_time
    sql: ${TABLE}.laborhourCreatedOn
  - dimension_group: updated_time
    type: date_time
    sql: ${TABLE}.laborhourUpdatedOn
  - measure: total_campaign_available_hrs
    type: sum
    sql: ${campaign_available_hrs}
  - measure: total_campaign_calls
    type: sum
    sql: ${campaign_calls}
  - measure: total_campaign_labor_hrs
    type: sum
    sql: ${campaign_labor_hrs}
  - measure: total_campaign_talk_hrs
    type: sum
    sql: ${campaign_talk_hrs}
  - measure: utilization
    type: number
    sql: NULLIF(${total_campaign_talk_hrs} + ${total_campaign_available_hrs}, 0) / NULLIF(${total_campaign_labor_hrs}, 0) * 100.0

Contents

  • labor_hour.view.lkml
  • labor_hour.view.lookml