view: int_dbt_prod_summary_territory_kpis {

  derived_table: {
    sql:

    with cy_targets as (
      select
        ifpi_country_group,
        market_type,
        kpi,
        to_number(cy_target, 38, 10) as cy_target,
        --to_number(cy_24_actual, 38, 10) as cy_24_actual
      from intelligence.dev_zallard.territory_kpi_targets
    ),

      target_driver_excl_ex_country_revenue as (
      select
      ifpi_country_group,
      market_type,
      kpi,
      to_number(target_driver, 38, 10) as target_driver
      from intelligence.dev_zallard.territory_kpi_targets
      where kpi != 'LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY'
      ),

      target_driver_ex_country_revenue_only as (
      select
      ifpi_country_group,
      market_type,
      kpi,
      target_driver
      from intelligence.dev_zallard.territory_kpi_targets
      where kpi = 'LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY'

      )

      select
      kpis.*,
      cy_targets.cy_target,
      --cy24_targets.cy_24_actual,
      target_driver_exlc.target_driver as target_driver_exlc,
      target_driver_only.target_driver as target_driver_only
      from intelligence.dbt_prod_strategy.ifpi_summary_territory_kpis kpis
      left join cy_targets cy_targets on cy_targets.ifpi_country_group = kpis.transaction_country and cy_targets.kpi = kpis.kpis
      left join target_driver_excl_ex_country_revenue target_driver_exlc on target_driver_exlc.ifpi_country_group = kpis.transaction_country and target_driver_exlc.kpi = kpis.kpis
      left join target_driver_ex_country_revenue_only target_driver_only on target_driver_only.ifpi_country_group = kpis.transaction_country and target_driver_only.kpi = kpis.kpis

      ;;

  }

  dimension_group: last_month_date {
    label: "Most Recent Month"
    type: time
    description: "Most recent activity month"
    sql: ${TABLE}.last_month_date ;;
    timeframes: [year,
      month,
      date,
      month_name,
      month_num]
  }

  parameter: country_group_type {
    type: string
    # allowed_value: { label: "Label Country" value: "label"}
    allowed_value: { label: "IFPI Country Groups" value: "ifpi"}
  }

  dimension: country {
    description: "The country or country group."
    type: string
    sql: ${TABLE}.transaction_country ;;
  }

  dimension: KPI {
    description: "The name of the KPI."
    type: string
    sql: ${TABLE}.kpis ;;
  }

  measure: last_month {
    description: "KPI values for the latest available month."
    type: sum
    sql: ${TABLE}.last_month ;;
    html:
          {% if int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_MARGIN" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% else %}
          {{ value }}
          {% endif %}
      ;;
  }

  measure: previous_month {
    description: "KPI values for the month prior to the latest available month."
    type: sum
    sql: ${TABLE}.previous_month ;;
    html:
          {% if int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_MARGIN" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% else %}
          {{ value }}
          {% endif %}
      ;;
  }

  measure: same_month_last_year {
    description: "KPI values for the same month as the latest month but in the previous year."
    type: sum
    sql: ${TABLE}.same_month_last_year ;;
    html:
          {% if int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_MARGIN" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% else %}
          {{ value }}
          {% endif %}
      ;;
  }

  measure: this_year_ytd {
    description: "KPI values that aggregates year to date data for the current year."
    type: sum
    sql: ${TABLE}.this_year_ytd ;;
    html:
          {% if int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_MARGIN" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% else %}
          {{ value }}
          {% endif %}
      ;;
  }

  measure: last_year_ytd {
    description: "KPI values that aggregates year to date data for the previous year."
    type: sum
    sql: ${TABLE}.last_year_ytd ;;
    html:
          {% if int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_MARGIN" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_REVENUE" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_GLOBAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "COUNTRY_MARKET_SHARE_LOCAL_STREAMS" %}
          {{ value | times: 100 | round:2 }}%
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_DISTRIBUTION_FEE_LOCAL_LABEL" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "LOCAL_LABEL_REVENUE_OUTSIDE_HOME_COUNTRY" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000 and value < 1000000 %}
          ${{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000 and value < 1000000000 %}
          ${{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "WORLD_WIDE_REVENUE_FROM_LOCAL_LABELS" and value >= 1000000000 and value < 1000000000000 %}
          ${{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORES_GLOBAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_LOCAL_LABEL_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "ORCHARD_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000 and value < 1000000 %}
          {{ value | divided_by: 1000 | round:2 }}K
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000 and value < 1000000000 %}
          {{ value | divided_by: 1000000 | round:2 }}M
          {% elsif int_dbt_prod_summary_territory_kpis.KPI._value == "TOTAL_MARKET_TOP_STORE_STREAMS" and value >= 1000000000 and value < 1000000000000 %}
          {{ value | divided_by: 1000000000 | round:2 }}B
          {% else %}
          {{ value }}
          {% endif %}
      ;;
  }

  dimension: target_driver_exlc {
    description: "Targer driver for KPIs that have a numeric target value"
    type: number
    value_format: "0.00%"
    sql: ${TABLE}.target_driver_exlc ;;
  }

  dimension: target_driver_only{
    description: "Targer driver for KPIs that non-numeric target value"
    type: string
    sql: ${TABLE}.target_driver_only ;;
  }

  dimension: CY25_Target {
    label: "CY Target"
    description: "Calendar Year targets for KPIs"
    type: number
    value_format: "0.00%"
    sql: ${TABLE}.cy_target ;;
  }

  # dimension: CY24_Actual {
  #   description: "H1 '24 Actual Data. Market share data for this field is sourced from IFPI."
  #   type: number
  #   value_format: "0.00%"
  #   sql: ${TABLE}.cy_24_actual ;;
  # }


}