view: awal_awal_finance_fpa_pr_revenue {
  sql_table_name: AWAL.AWAL_FINANCE.FPA_PR_AWAL;;

    dimension: awal_client_segment {
      type: string
      sql: ${TABLE}.awal_client_segment ;;
    }

  dimension: awal_client_tier {
    type: string
    sql: ${TABLE}.awal_client_tier ;;
  }

  dimension: label_service_agreement_type {
    type: string
    sql: ${TABLE}.label_service_agreement_type ;;
  }

  dimension: agreement_id {
    type: string
    sql: ${TABLE}.agreement_id ;;
  }

  dimension: agreement_is_novated {
    type: string
    sql: ${TABLE}.agreement_is_novated ;;
  }

  dimension: recrd_artist_name {
    type: string
    sql: ${TABLE}.recrd_artist_name;;
  }


  dimension: artist_name {
    type: string
    sql: ${TABLE}.artist_name;;
  }

  dimension: fund_or_not {
    type: string
    sql: ${TABLE}.fund_or_not;;
  }

  dimension: client_rights {
    type: string
    sql: ${TABLE}.client_rights;;
  }

  dimension: agreement_is_terminated {
    type: string
    sql: ${TABLE}.agreement_is_terminated;;
  }

  dimension: agreement_terminated_date {
    type: date
    sql: ${TABLE}.agreement_terminated_date;;
  }

  dimension: outgoing_agreement_type {
    type: string
    sql: ${TABLE}.outgoing_agreement_type;;
  }

  dimension: acquirer_id {
    type: string
    sql: ${TABLE}.acquirer_id;;
  }

  dimension: acquirer {
    type: string
    sql: ${TABLE}.acquirer;;
  }

  dimension: assignor_id {
    label: "Client ID"
    type: string
    sql: ${TABLE}.assignor_id;;
  }

  dimension: assignor {
    label: "Client Name"
    type: string
    sql: ${TABLE}.assignor;;
  }

  dimension: client_ip_type {
    type: string
    sql: ${TABLE}.client_ip_type;;
  }

  dimension: stat_year {
    type: date
    sql: ${TABLE}.stat_year;;
  }

  dimension: stat_q_date {
    type: date
    sql: ${TABLE}.stat_q_date;;
  }

  dimension: stat_q {
    type: string
    sql: ${TABLE}.stat_q;;
  }

  dimension_group: stat_mnth_date {
    label: "stat month"
    type: time
    sql: ${TABLE}.stat_mnth_date ;;
    timeframes: [
      date,
      month,
      month_num,
      month_name,
      quarter,
      quarter_of_year,
      year,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year
    ]
  }

  dimension: stat_month {
    type: string
    sql: ${TABLE}.stat_mnth;;
  }

  dimension: stat_month_FY {
    type: string
    sql: case
          when ${TABLE}.stat_mnth IN ('2012M01','2012M02','2012M03') then 'FY 2012'
          when ${TABLE}.stat_mnth IN ('2012M04','2012M05','2012M06','2012M07','2012M08','2012M09','2012M10','2012M11','2012M12','2013M01','2013M02','2013M03') then 'FY 2013'
          when ${TABLE}.stat_mnth IN ('2013M04','2013M05','2013M06','2013M07','2013M08','2013M09','2013M10','2013M11','2013M12','2014M01','2014M02','2014M03') then 'FY 2014'
          when ${TABLE}.stat_mnth IN ('2014M04','2014M05','2014M06','2014M07','2014M08','2014M09','2014M10','2014M11','2014M12','2015M01','2015M02','2015M03') then 'FY 2015'
          when ${TABLE}.stat_mnth IN ('2015M04','2015M05','2015M06','2015M07','2015M08','2015M09','2015M10','2015M11','2015M12','2016M01','2016M02','2016M03') then 'FY 2016'
          when ${TABLE}.stat_mnth IN ('2016M04','2016M05','2016M06','2016M07','2016M08','2016M09','2016M10','2016M11','2016M12','2017M01','2017M02','2017M03') then 'FY 2017'
          when ${TABLE}.stat_mnth IN ('2017M04','2017M05','2017M06','2017M07','2017M08','2017M09','2017M10','2017M11','2017M12','2018M01','2018M02','2018M03') then 'FY 2018'
          when ${TABLE}.stat_mnth IN ('2018M04','2018M05','2018M06','2018M07','2018M08','2018M09','2018M10','2018M11','2018M12','2019M01','2019M02','2019M03') then 'FY 2019'
          when ${TABLE}.stat_mnth IN ('2019M04','2019M05','2019M06','2019M07','2019M08','2019M09','2019M10','2019M11','2019M12','2020M01','2020M02','2020M03') then 'FY 2020'
          when ${TABLE}.stat_mnth IN ('2020M04','2020M05','2020M06','2020M07','2020M08','2020M09','2020M10','2020M11','2020M12','2021M01','2021M02','2021M03') then 'FY 2021'
          when ${TABLE}.stat_mnth IN ('2021M04','2021M05','2021M06','2021M07','2021M08','2021M09','2021M10','2021M11','2021M12','2022M01','2022M02','2022M03') then 'FY 2022'
          when ${TABLE}.stat_mnth IN ('2022M04','2022M05','2022M06','2022M07','2022M08','2022M09','2022M10','2022M11','2022M12','2023M01','2023M02','2023M03') then 'FY 2023'
          else ${TABLE}.stat_mnth
          end;;
          }


  dimension: bankreceipt_cur {
    type: string
    sql: ${TABLE}.bankreceipt_cur;;
  }

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

  dimension: salesforce_opportunity_id {
    type: string
    sql: ${TABLE}.salesforce_opportunity_id;;
  }

  dimension: salesforce_opportunity_name {
    type: string
    sql: ${TABLE}.salesforce_opportunity_name;;
  }

    dimension_group: agreement_signed_date {
      label: "agreement signed date"
      type: time
      sql: ${TABLE}.agreement_signed_date ;;
      timeframes: [
        date,
        month,
        month_num,
        month_name,
        quarter,
        quarter_of_year,
        year,
        fiscal_month_num,
        fiscal_quarter,
        fiscal_quarter_of_year,
        fiscal_year
      ]
  }

  dimension: fund_group_name {
    type: string
    sql: ${TABLE}.fund_group_name;;
  }

  dimension: rt_desc {
    type: string
    sql: ${TABLE}.rt_desc;;
  }

  dimension: rt_id {
    type: string
    sql: ${TABLE}.rt_id;;
  }

  dimension: rt_type_code {
    type: string
    sql: ${TABLE}.rt_type_code;;
  }

  dimension: rt_reporting {
    type: string
    sql: ${TABLE}.rt_reporting;;
  }

  dimension: rt_groups {
    type: string
    sql: ${TABLE}.rt_groups;;
  }

  dimension: territory_desc {
    type: string
    map_layer_name: countries
    sql: ${TABLE}.territory_desc;;
  }

  dimension: territory_id {
    type: string
    sql: ${TABLE}.territory_id;;
  }

  dimension: source_id {
    type: string
    sql: ${TABLE}.source_id;;
  }

  dimension: source_name {
    type: string
    sql: ${TABLE}.source_name;;
  }

  dimension: source_name_group {
    type: string
    sql: case
          when contains(upper(${TABLE}.source_name), 'SPOTIFY') then 'Spotify'
          when contains(upper(${TABLE}.source_name), 'AMAZON') then 'Amazon'
          when contains(${TABLE}.source_name, "Apple Distribution International (iTunes EU)","Apple Inc.","Apple Inc. (iTunes Canada)","Apple Inc. (iTunes USA)","Apple Pty Limited (iTunes Aus & NZ)","Apple Services, LATAM LLC","iTunes KK (iTunes Japan)") then 'Apple'
          when contains(upper(${TABLE}.source_name), 'DEEZER') then 'Deezer'
          when contains(upper(${TABLE}.source_name), 'FACEBOOK') then 'Facebook'
          else ${TABLE}.source_name
          end ;;
  }

  dimension: source_name_group_other {
    type: string
    sql: case
          when contains(upper(${TABLE}.source_name), 'SPOTIFY') then 'Spotify'
          when contains(upper(${TABLE}.source_name), 'AMAZON') then 'Amazon'
          when contains(${TABLE}.source_name, "Apple Distribution International (iTunes EU)","Apple Inc.","Apple Inc. (iTunes Canada)","Apple Inc. (iTunes USA)","Apple Pty Limited (iTunes Aus & NZ)","Apple Services, LATAM LLC","iTunes KK (iTunes Japan)") then 'Apple'
          when contains(upper(${TABLE}.source_name), 'DEEZER') then 'Deezer'
          when contains(upper(${TABLE}.source_name), 'PANDORA') then 'Pandora'
          when contains(upper(${TABLE}.source_name), 'YOUTUBE') then 'YouTube'
          when contains(upper(${TABLE}.source_name), 'VEVO') then 'Vevo'
          when contains(upper(${TABLE}.source_name), 'PROPER MUSIC') then 'Proper Music Distribution'
          when contains(upper(${TABLE}.source_name), 'ADA UK') then 'ADA UK'
          when contains(upper(${TABLE}.source_name), 'ALLIANCE ENTERTAINMENT') then 'Alliance Entertainment'
          when contains(upper(${TABLE}.source_name), 'SOUNDCLOUD') then 'SoundCloud'
          when contains(upper(${TABLE}.source_name), 'ROUGH TRADE') then 'Rough Trade'
          when contains(upper(${TABLE}.source_name), 'INERTIA') then 'Inertia Australia'
           when contains(upper(${TABLE}.source_name), 'TIKTOK') then 'TikTok'
          else 'All Other'
          end ;;
  }

  dimension: territory_desc_group {
    type: string
    sql: case
          when ${TABLE}.territory_desc IN ('UK', 'France', 'Germany') then 'Europe'
          else 'Row'
          end;;
  }

  dimension: revenue_source_name {
    type: string
    sql: ${TABLE}.revenue_source_name;;
  }

  dimension: revenue_source_id {
    type: string
    sql: ${TABLE}.revenue_source_id;;
  }

  measure: USDHIST_SOURCE {
    type: sum
    sql: ${TABLE}.USDHIST_SOURCE ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_RCVD {
    type: sum
    sql: ${TABLE}.USDHIST_RCVD ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_REVENUE {
    type: sum
    sql: ${TABLE}.USDHIST_REVENUE ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_REVENUE_C {
    type: sum
    sql: ${TABLE}.USDHIST_REVENUE_C ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_CLIENT {
    type: sum
    sql: ${TABLE}.USDHIST_CLIENT ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_CLIENT_C {
    type: sum
    sql: ${TABLE}.USDHIST_CLIENT_C ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_MARGIN {
    type: sum
    sql: ${TABLE}.USDHIST_MARGIN ;;
    value_format: "$#,##0.00"
  }

  measure: USDHIST_DIR_COLL_FEE {
    type: sum
    sql: ${TABLE}.USDHIST_DIR_COLL_FEE ;;
    value_format: "$#,##0.00"
  }

  measure: awal_perc {
    label: "AWAL %"
    type:  number
    sql: sum(${TABLE}.USDHIST_MARGIN) / nullif(sum(${TABLE}.USDHIST_REVENUE_C),0);;
    value_format: "0.00%"
  }

  measure: ACCTCUR_SOURCE {
    type: sum
    sql: ${TABLE}.ACCTCUR_SOURCE ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_RECEIVED {
    type: sum
    sql: ${TABLE}.ACCTCUR_RECEIVED ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_REVENUE {
    type: sum
    sql: ${TABLE}.ACCTCUR_REVENUE ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_REVENUE_C {
    type: sum
    sql: ${TABLE}.ACCTCUR_REVENUE_C ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_CLIENT {
    type: sum
    sql: ${TABLE}.ACCTCUR_CLIENT ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_CLIENT_C {
    type: sum
    sql: ${TABLE}.ACCTCUR_CLIENT_C ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_MARGIN {
    type: sum
    sql: ${TABLE}.ACCTCUR_MARGIN ;;
    value_format: "#,##0.00"
  }

  measure: ACCTCUR_DIR_COLL_FEE {
    type: sum
    sql: ${TABLE}.ACCTCUR_DIR_COLL_FEE ;;
    value_format: "#,##0.00"

}
}