- dimension: chadleys_favorite_trans_types ## MANUALLY EDITED FIELD
    view_label: Transaction
    label: MGMT Monthly Accounting Transaction Types
    description: 'Chadelyn Stark is King of the North'
    sql_case: 
      'Ad Supported': ${TABLE}.transac_type_abbr in ('AS', 'AV')
      'Download Albums': ${TABLE}.transac_type_abbr in ('DA', 'UT')
      'Download Tracks': ${TABLE}.transac_type_abbr in ('DT', 'DV', 'DR', 'RB')
      'Subscription': ${TABLE}.transac_type_abbr in ('S', 'SV', 'TD','CL','TA')
      'Video': ${TABLE}.transac_type_abbr in ('NT','TH','VS', 'VR', 'TL','PH')
      else: 'Other'
    type: string


  - dimension: units_cat
    view_label: Sales
    label: Units Cat
    description: ''
    sql: ${TABLE}.units
    hidden: true
    type: number
    decimals: 0
    value_format: '[>=1000000]#,##0.0,," M"; #,###;'
    
  - measure: effective_units_calc
    view_label: Sales
    label: Effective Units
    description: 'Based on 1DT in iTunes in US'
    type: sum
    sql: ${units_cat} * ${effective_units.mult_factor}
    value_format: '[>=1000000]#,##0.0,," M"; #,###'

  - measure: netflix_tl
    view_label: Video
    label: Netflix TL Gross USD
    type: sum
    sql: ${TABLE}.gross_revenue_usd
    filters:
      store_id: 450
      transac_type_abbr: 'TL'

  - measure: vod_volume
    view_label: Video
    label: VOD Volume (Units)
    type: sum
    sql: ${TABLE}.units
    filters:
      transac_type_abbr: 'VR'  

  - measure: ivod_volume
    view_label: Video
    label: iVOD Volume (Units)
    type: sum
    sql: ${TABLE}.units
    filters:
      store_id: not 554, 555
      transac_type_abbr: 'VR'     

  - measure: est_plus_vod_volume
    view_label: Video
    label: EST + VOD Volume (Units)
    type: sum
    sql: ${TABLE}.units
    filters: 
      transac_type_abbr: 'VR, DV'

  - measure: dbo_gross_sales_usd
    view_label: Video
    label: DBO Gross USD
    type: sum
    sql: ${TABLE}.gross_revenue_usd
    filters: 
      transac_type_abbr: 'TH'  

  - measure: us_cananda_bo_gross_sales_usd
    view_label: Video
    label: US and Canada Box Office Gross USD
    type: sum
    sql: ${TABLE}.gross_revenue_usd
    filters: 
      transac_type_abbr: 'TH'
      country_id: 1,2
      
  - dimension: internet_cable_normalized_transtype
    view_label: Transaction
    label: Internet Cable Normalized Transtype
    description: 'Used by Finance and Film/TV to determine when Advances should be paid out'
    case_sensitive: FALSE
    suggestable: FALSE
    sql: |  
      CASE
      when ${TABLE}.store_name like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'DV' then 'cEST'
      when ${TABLE}.store_name not like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'DV' then 'iEST'
      when ${TABLE}.store_name like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'VR' then 'cVOD'
      when ${TABLE}.store_name not like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'VR' then 'iVOD'
      when ${TABLE}.transac_type_abbr = 'AV' then 'AVOD'
      when ${TABLE}.transac_type_abbr = 'TH' then 'Theatrical'
      when ${TABLE}.transac_type_abbr = 'PH' then 'Physical'
      when ${TABLE}.transac_type_abbr = 'TL' then 'Netflix/PayTV'
      when ${TABLE}.transac_type_abbr = 'NT' then 'Non-Theatrical'
      when ${TABLE}.transac_type_abbr = 'SV' then 'SVOD'
      END
    type: string
    
  - dimension: rolled_up_normalized_transtype
    view_label: Transaction
    label: Rolled Up Normalized Transtype
    description: 'Used by Finance and Film/TV to determine when Advances should be paid out'
    case_sensitive: FALSE
    suggestable: FALSE
    sql: |  
      CASE
      when ${TABLE}.transac_type_abbr = 'DV' then 'EST'
      when ${TABLE}.transac_type_abbr = 'AV' then 'AVOD'
      when ${TABLE}.transac_type_abbr = 'TH' then 'Theatrical'
      when ${TABLE}.transac_type_abbr = 'PH' then 'Physical'
      when ${TABLE}.transac_type_abbr = 'TL' then 'Netflix/PayTV'
      when ${TABLE}.transac_type_abbr = 'NT' then 'Non-Theatrical'
      when ${TABLE}.transac_type_abbr = 'SV' then 'SVOD'
      when ${TABLE}.transac_type_abbr = 'VR' then 'VOD'
      END
    type: string    

  - dimension: film_tv_vs_music_by_product
    view_label: Bucket
    label: Music vs Film/TV by Product Type
    description: 'Music (includes Web videos) vs Film/TV'
    sql_case: 
      'Music': ${TABLE}.release_product_type in ('Music','Web Videos')
      'Film/TV': ${TABLE}.release_product_type in ('Movie', 'TV Show')
      else: 'Other'
    type: string
    
  - dimension: label_is_red
    view_label: Label
    label: Is Red 
    description: 'Filters out RED labels'
    sql: |
      ${TABLE}.label_ownerid = 113
    type: yesno
    
  - dimension: last_12_months_activity
    view_label: Date
    label: Last 12 Months of Activity
    description: 'Last 12 Months of Activity and 12 Months Prior'
    sql: |
        CASE WHEN activity_month >= '2015-04-01' and ${TABLE}.activity_month <'2016-04-01' THEN 'FY2016' 
            WHEN activity_month >= '2014-04-01' and ${TABLE}.activity_month <'2015-04-01' THEN 'FY2015'
            ELSE 'Rest of activity' 
        END
    type: string 


  - dimension: sme_affiliate_label_country_groupings ## MANUALLY EDITED FIELD
    view_label: Territory
    label: SME Affiliates - Label Country Specified Territories
    description: 'Territory Groupings based on SME Offices around the World'
    sql_case:   
      'Argentina': ${TABLE}.label_country in ('Argentina','Bolivia','Paraguay','Uruguay')
      'Australia': ${TABLE}.label_country in ('Australia','Cook Islands','Christmas Island','Micronesia','Kiribati','Northern Mariana Islands','Norfolk Island','Nauru','Niue','Solomon Islands','Timor-Leste','Tonga','Tuvalu','Vanuatu')
      'Austria': ${TABLE}.label_country in ('Austria')
      'Belgium': ${TABLE}.label_country in ('Belgium','Luxembourg')
      'Brazil': ${TABLE}.label_country in ('Brazil')
      'Canada': ${TABLE}.label_country in ('Canada')
      'Chile': ${TABLE}.label_country in ('Chile')
      'China': ${TABLE}.label_country in ('China')
      'Colombia': ${TABLE}.label_country in ('Colombia','Ecuador','Peru')
      'Costa Rica': ${TABLE}.label_country in ('Antigua and Barbuda','Anguilla','Aruba','Barbados','Bermuda','Bahamas','Belize','Costa Rica','Cuba','Dominica','Dominican Republic','Grenada','Guatemala','Guyana','Honduras','Haiti','Jamaica','Saint Kitts and Nevis','Cayman Islands','Saint Lucia','Montserrat','Nicaragua','Panama','Suriname','El Salvador','Turks And Caicos Islands','Trinidad and Tobago','Saint Vincent and the Grenadines','British Virgin Islands','US Virgin Islands')
      'Czech Republic': ${TABLE}.label_country in ('Czech Republic','Slovakia')
      'Denmark': ${TABLE}.label_country in ('Denmark','Faroe Islands','Iceland')
      'Finland': ${TABLE}.label_country in ('Ã…land Islands','Estonia','Finland','Lithuania','Latvia')
      'France': ${TABLE}.label_country in ('Andorra','Algeria','France','French Guiana','Guadeloupe','Morocco','Monaco','Martinique','New Caledonia','French Polynesia','Saint Pierre And Miquelon','Réunion','Tunisia','Wallis And Futuna','Mayotte')
      'Germany': ${TABLE}.label_country in ('Germany')
      'Great Britain': ${TABLE}.label_country in ('United Kingdom','Guernsey','Gibraltar','Isle Of Man','Jersey')
      'Greece': ${TABLE}.label_country in ('Greece')
      'Hong Kong': ${TABLE}.label_country in ('Fiji','Hong Kong','British Indian Ocean Territory','Marshall Islands','Myanmar','Macau','Palau','Vietnam')
      'Hungary': ${TABLE}.label_country in ('Hungary')
      'India': ${TABLE}.label_country in ('Bangladesh','Bhutan','India','Sri Lanka','Maldives','Nepal','Pakistan')
      'Indonesia': ${TABLE}.label_country in ('Indonesia','Papua New Guinea')
      'Ireland': ${TABLE}.label_country in ('Ireland')
      'Italy': ${TABLE}.label_country in ('Italy','Malta','San Marino','Holy See')
      'Japan': ${TABLE}.label_country in ('Japan')
      'Malaysia': ${TABLE}.label_country in ('Brunei','Malaysia')
      'Mexico': ${TABLE}.label_country in ('Mexico')
      'Middle East': ${TABLE}.label_country in ('United Arab Emirates','Afghanistan','Bahrain','Egypt','Iraq','Iran','Jordan','Kuwait','Lebanon','Oman','Palestinian Territory','Qatar','Saudi Arabia','Syria','Yemen')
      'Netherlands': ${TABLE}.label_country in ('Netherlands Antilles','Netherlands')
      'New Zealand': ${TABLE}.label_country in ('New Zealand','Samoa')
      'Norway': ${TABLE}.label_country in ('Norway','Svalbard and Jan Mayen')
      'Other Europe Regional ': ${TABLE}.label_country in ('Albania','Bosnia and Herzegovina','Bulgaria','Serbia and Montenegro','Cyprus','Greenland','Croatia','Israel','Macedonia','Romania','Slovenia')
      'Philippines': ${TABLE}.label_country in ('Philippines')
      'Poland': ${TABLE}.label_country in ('Poland')
      'Portugal': ${TABLE}.label_country in ('Portugal')
      'Rep. Korea': ${TABLE}.label_country in ('North Korea','South Korea')
      'ROW': ${TABLE}.label_country in ('Antarctica','Bouvet Island','Cocos (Keeling) Islands','Falkland Islands (Malvinas)','South Georgia and the South Sandwich Islands','Heard Island and McDonald Islands','Pitcairn Islands','French Southern and Antarctic Lands','Tokelau','US Minor Outlying Islands')
      'Russia': ${TABLE}.label_country in ('Armenia','Azerbaijan','Belarus','Georgia','Kyrgyzstan','Kazakhstan','Moldova','Mongolia','Russia','Tajikistan','Turkmenistan','Ukraine','Uzbekistan')
      'Singapore': ${TABLE}.label_country in ('Cambodia','Laos','Singapore')
      'South Africa': ${TABLE}.label_country in ('Angola','Burkina Faso','Burundi','Benin','Botswana','Congo','Central African Republic','Congo','Ivory Coast','Cameroon','Cape Verde','Djibouti','Western Sahara','Eritrea','Ethiopia','Gabon','Ghana','Gambia','Guinea','Equatorial Guinea','Guinea-Bissau','Kenya','Comoros','Liberia','Lesotho','Libya','Madagascar','Mali','Mauritania','Mauritius','Malawi','Mozambique','Namibia','Niger','Nigeria','Rwanda','Seychelles','Sudan','Saint Helena','Sierra Leone','Senegal','Somalia','São Tomé and Príncipe','Swaziland','Chad','Togo','Tanzania','Uganda','South Africa','Zambia','Zimbabwe')
      'Spain': ${TABLE}.label_country in ('Spain')
      'Sweden': ${TABLE}.label_country in ('Sweden')
      'Switzerland': ${TABLE}.label_country in ('Switzerland','Liechtenstein')
      'Taiwan': ${TABLE}.label_country in ('Taiwan')
      'Thailand': ${TABLE}.label_country in ('Thailand')
      'Turkey': ${TABLE}.label_country in ('Turkey')
      'USA': ${TABLE}.label_country in ('American Samoa','Guam','Puerto Rico','USA')
      'Venezuela': ${TABLE}.label_country in ('Venezuela')
      else: 'ROW'
    type: string