view: stmt_db_dig_sales_analytics { sql_table_name: dig_sales_analytics ;; # dimension: performance_rights_substores { # view_label: "Statement" # label: "Perf. Rights Sub-Stores" # # THIS DIMENSION IS DEPRECIATED BECAUSE IT'S NOW A TABLE. CODE LEFT FOR POSTERITY. # sql: CASE # WHEN ${actual_statement_no} LIKE "%Pandora%" THEN "Pandora" # WHEN ${actual_statement_no} LIKE "%SXM%" OR ${actual_statement_no} LIKE "%SDARS%" THEN "Sirius" # WHEN ${actual_statement_no} LIKE "%Batanga%" THEN "Batanga" # WHEN ${actual_statement_no} LIKE "%GooglePlay%" THEN "Google Play" # WHEN ${actual_statement_no} LIKE "%iHeart%" THEN "iHeart" # WHEN ${actual_statement_no} LIKE "%Spotify%" THEN "Spotify" # WHEN ${actual_statement_no} LIKE "%Univision%" THEN "Univision" # WHEN ${actual_statement_no} LIKE "%SBS%" THEN "Spanish Broadcast System" # ELSE "Other" # END # ;; # } # dimension: performance_rights_subtransac { # view_label: "Statement" # label: "Perf. Rights Sub-Transactions" # # THIS DIMENSION IS DEPRECIATED BECAUSE IT'S NOW A TABLE. CODE LEFT FOR POSTERITY. # sql: CASE # WHEN ${actual_statement_no} LIKE "%BES%" THEN "Business Establishment" # WHEN ${actual_statement_no} LIKE "%Webcasting%" OR ${actual_statement_no} LIKE "%GooglePlay%" OR ${actual_statement_no} LIKE "%Batanga%" OR ${actual_statement_no} LIKE "%SBS%" OR ${actual_statement_no} LIKE "%Spotify%" OR ${actual_statement_no} LIKE "%iHeart%" OR ${actual_statement_no} LIKE "%Pandora%" OR ${actual_statement_no} LIKE "%Univision%" THEN "Webcasting" # WHEN ${actual_statement_no} LIKE "%CableRadio%" THEN "Cable Radio" # WHEN ${actual_statement_no} LIKE "%PES%" THEN "Pre-existing Subscription Service" # WHEN ${actual_statement_no} LIKE "%Foreign Royalties%" THEN "Foreign Royalties" # WHEN ${actual_statement_no} LIKE "%SDARS%" THEN "Pre-existing Satellite Radio Service" # WHEN ${actual_statement_no} LIKE "%OnDemand%" THEN "On Demand" # ELSE "Other" # END # ;; # } dimension: flavors { view_label: "Statement" label: "Flavor" sql: CASE WHEN ${actual_statement_no} LIKE "%_DRA%" THEN "DRA" WHEN ${actual_statement_no} LIKE "%_DMGI%" THEN "DMGI" WHEN ${actual_statement_no} LIKE "%_MFLP%" THEN "MFLP" WHEN ${actual_statement_no} LIKE "%_PSY%" THEN "PSY" WHEN ${actual_statement_no} LIKE "%_TVT%" THEN "TVT" WHEN ${actual_statement_no} LIKE "%_HRS%" THEN "HRS" when ${actual_statement_no} LIKE "%_ABBD%" THEN "ABBD" when ${actual_statement_no} LIKE "ABBD_PHYS%" THEN "ABBD" when ${actual_statement_no} LIKE "%_FLUV%" THEN "FLUV" when ${actual_statement_no} LIKE "%_ALTF%" THEN "ALTF" when ${actual_statement_no} LIKE "ALTF_PHYS%" THEN "ALTF" WHEN ${actual_statement_no} LIKE "%YouTube%Music%" THEN "ORCD" WHEN ${actual_statement_no} LIKE "%YouTube%BFM%" THEN "BFM" WHEN ${actual_statement_no} LIKE "%YouTube%IODA%" THEN "IODA" WHEN ${actual_statement_no} LIKE "%YouTube%NVE%" THEN "NAIVE" WHEN ${actual_statement_no} LIKE "%YouTube%FTUN%" THEN "FTUN" WHEN ${actual_statement_no} LIKE "%YouTube%PHON%" THEN "PHON" WHEN ${actual_statement_no} LIKE "%YouTube%DGDI%" THEN "DGDI" WHEN ${actual_statement_no} LIKE "%YouTube%AWAL%" THEN "AWAL" WHEN ${actual_statement_no} LIKE "%YouTube%" THEN "DMGI" WHEN ${actual_statement_no} LIKE "itunes%_PRI%" THEN "PRI" WHEN ${actual_statement_no} LIKE "itunes%_CKV%" THEN "CKV" WHEN ${actual_statement_no} LIKE "itunes%_ROT%" THEN "ROT" WHEN ${actual_statement_no} LIKE "itunes%_RDNA%" THEN "RDNA" WHEN ${actual_statement_no} LIKE "itunes%_RDWW" THEN "RDWW" WHEN ${actual_statement_no} LIKE "itunes%_SFR%" THEN "SFR" WHEN ${actual_statement_no} LIKE "itunes%_BFMA%" THEN "BFMA" WHEN ${actual_statement_no} LIKE "itunes%_ARTP%" THEN "ARTP" WHEN ${actual_statement_no} LIKE "itunes%_AWL2%" THEN "AWL2" WHEN ${actual_statement_no} LIKE "itunes%_AWL3%" THEN "AWL3" WHEN ${actual_statement_no} LIKE "%_RLPS%" THEN "RLPS" WHEN ${actual_statement_no} LIKE "%_UPL%" THEN "UPL" /* has to be above ioda 'cuz of rhapsody */ WHEN ${actual_statement_no} LIKE "%_IODA%" THEN "IODA" WHEN ${actual_statement_no} LIKE "%_IRIS%" THEN "IRIS" WHEN ${actual_statement_no} LIKE "%_BFM%" THEN "BFM" WHEN ${actual_statement_no} LIKE "%_NVE%" THEN "NAIVE" WHEN ${actual_statement_no} LIKE "%KNR2%" THEN "KNR2" WHEN ${actual_statement_no} LIKE "%KNRA%" THEN "KNRA" WHEN ${actual_statement_no} LIKE "%KNRR%" THEN "KNRR" WHEN ${actual_statement_no} LIKE "%KNRS%" THEN "KNRS" WHEN ${actual_statement_no} LIKE "%KNRU%" THEN "KNRU" WHEN ${actual_statement_no} LIKE "%KNR%" THEN "KNR" WHEN ${actual_statement_no} LIKE "%_AWAL%" THEN "AWAL" WHEN ${actual_statement_no} LIKE "%_RESS%" THEN "RESS" WHEN ${actual_statement_no} LIKE "Proper_PHYS%" THEN "RESS" WHEN ${actual_statement_no} LIKE "MRC%" THEN "RESS" WHEN ${actual_statement_no} LIKE "%_FTUN%" THEN "FTUN" WHEN ${actual_statement_no} LIKE "%_PHON%" AND ${actual_statement_no} NOT LIKE "%_PHYS%" THEN "PHON" WHEN ${actual_statement_no} LIKE "%_FNCB%" THEN "FNCB" WHEN ${actual_statement_no} LIKE "%_BLPG%" THEN "BLPG" WHEN ${actual_statement_no} LIKE "%_SHP%" THEN "SHP" /* has to exclude "FreshPlanet" */ WHEN ${actual_statement_no} LIKE "%_DGDI%" THEN "DGDI" WHEN ${actual_statement_no} LIKE "Bandcamp%RLPS" THEN "RLPS" /* reminder for the future: TNO is not a flavor - it is handled via getService */ ELSE "ORCD" END ;; type: string description: "Internal grouping code for stores that report sales under separate reports, typically for merged companies or acquired content." } dimension: service { view_label: "Statement" label: "Service" #X# Invalid LookML inside "dimension": {"description":null} sql: CASE WHEN ${actual_statement_no} LIKE "%TERM%LICENSE%" THEN "Term License Revenue" WHEN ${actual_statement_no} LIKE "REDRES[%" THEN "RED Residual Revenue" WHEN ${actual_statement_no} LIKE "VIDEO_1091%" THEN "1091 Revenue" WHEN ${actual_statement_no} LIKE "iTunes%ORCD%{TNO}%" THEN "1091 iTunes Revenue (Not Delivered to OA)" WHEN ${actual_statement_no} LIKE "Fuga%HRS%" THEN "Fuga HRS Revenue" WHEN ${actual_statement_no} LIKE "Fuga%ABBD%" THEN "Fuga ABBD Revenue" WHEN ${actual_statement_no} LIKE "%PHYSICAL_DVD%" THEN "Physical Film DVD Revenue" WHEN ${actual_statement_no} LIKE "%PHYS%" AND ${actual_statement_no} NOT LIKE "%PHYSFI${actual_statement_no}%" THEN "Physical Audio Revenue" WHEN ${actual_statement_no} LIKE "%BREAKAGE%" THEN "Breakage Allocation" WHEN ${actual_statement_no} LIKE "SoundExchange%Pandora%" THEN "SoundExchange: Pandora" WHEN ${actual_statement_no} LIKE "SoundExchange%SXM_OnDemand%" THEN "SoundExchange: SiriusXM On Demand Service" WHEN ${actual_statement_no} LIKE "SoundExchange%SXM_Webcasting%" THEN "SoundExchange: SiriusXM Webcasting" WHEN ${actual_statement_no} LIKE "SoundExchange%iHeart%" AND ${actual_statement_no} NOT LIKE "%BES%" THEN "SoundExchange: iHeart Radio" WHEN ${actual_statement_no} LIKE "SoundExchange%GooglePlay%" THEN "SoundExchange: Google Play" WHEN ${actual_statement_no} LIKE "SoundExchange%Univision%" THEN "SoundExchange: Univision" WHEN ${actual_statement_no} LIKE "SoundExchange%Batanga%" THEN "SoundExchange: Batanga" WHEN ${actual_statement_no} LIKE "SoundExchange%Spotify%" THEN "SoundExchange: Spotify" WHEN ${actual_statement_no} LIKE "SoundExchange%SBS%" THEN "SoundExchange: SBS" WHEN ${actual_statement_no} LIKE "SoundExchange%Webcasting%" THEN "SoundExchange: Webcasting" WHEN ${actual_statement_no} LIKE "SoundExchange%SXM_SDARS%" THEN "SoundExchange: SiriusXM SDARS" WHEN ${actual_statement_no} LIKE "SoundExchange%SDARS%" THEN "SoundExchange: SDARS" WHEN ${actual_statement_no} LIKE "SoundExchange%PES%" THEN "SoundExchange: PES" WHEN ${actual_statement_no} LIKE "SoundExchange%SXM_CableRadio%" THEN "SoundExchange: SiriusXM Cable Radio" WHEN ${actual_statement_no} LIKE "SoundExchange%CableRadio%" THEN "SoundExchange: Cable Radio" WHEN ${actual_statement_no} LIKE "SoundExchange%iHeart_BES%" THEN "SoundExchange: iHeart Radio BES" WHEN ${actual_statement_no} LIKE "SoundExchange%SXM_BES%" THEN "SoundExchange: SiriusXM BES" WHEN ${actual_statement_no} LIKE "SoundExchange%BES%" THEN "SoundExchange: BES" WHEN ${actual_statement_no} LIKE "SoundExchange%ForeignRoyalties%" THEN "SoundExchange: Foreign Royalties" WHEN ${actual_statement_no} LIKE "SoundExchange%Settlement%" THEN "SoundExchange: Settlement" WHEN ${actual_statement_no} LIKE "SoundExchange%TrueUp%" THEN "SoundExchange: True Up" WHEN ${actual_statement_no} LIKE "Amazon_Free%" THEN "Ad-Supported Streaming Audio" WHEN ${actual_statement_no} LIKE "Amazon_Cloud%" THEN "Cloud" WHEN ${actual_statement_no} LIKE "Amazon_Digital%" THEN "Downloads" WHEN ${actual_statement_no} LIKE "Amazon_DOD%VIDEO%" THEN "Disc On Demand DVD" WHEN ${actual_statement_no} LIKE "Amazon_DOD%" THEN "Disc On Demand CD" WHEN ${actual_statement_no} LIKE "Amazon_Prime%" AND ${actual_statement_no} NOT LIKE "%Audio%" THEN "Subscription Streaming Video" WHEN ${actual_statement_no} LIKE "Amazon_Prime_Audio%" THEN "Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Amazon_VOD%" THEN "Video On Demand" WHEN ${actual_statement_no} LIKE "Amazon_Unlimited%" THEN "Premium & Mid-Tier Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Google_Locker%" THEN "Cloud" WHEN ${actual_statement_no} LIKE "Google_Music%" THEN "Downloads" WHEN ${actual_statement_no} LIKE "Google_Streams%" THEN "Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Google_Radio%" THEN "Non-Interactive Radio" WHEN ${actual_statement_no} LIKE "Tidal_Free%" THEN "Ad-Supported Streaming Radio" WHEN ${actual_statement_no} LIKE "Tidal%" THEN "Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Tiktok_Escrow%" THEN "Resolved Short Form UGC Streaming Video" WHEN ${actual_statement_no} LIKE "Tiktok\_%" THEN "Short Form UGC Streaming Video" WHEN ${actual_statement_no} LIKE "Spotify_Fraudulent%" THEN "Fraudulent Stream Plays" WHEN ${actual_statement_no} LIKE "Spotify_Penalties%" THEN "Fraudulent Stream Penalties" WHEN ${actual_statement_no} LIKE "Spotify_Discovery%" THEN "Spotify Discovery Marketing Deductions" WHEN ${actual_statement_no} LIKE "Spotify_Spoken_Word%" THEN "Spotify Spoken Word Content Penalty" WHEN ${actual_statement_no} LIKE "Spotify_Short_Noise%" THEN "Spotify Short Noise Plays (unmonetized)" WHEN ${actual_statement_no} LIKE "Spotify_Unqualified%" THEN "Spotify Unqualified Plays (unmonetized)" WHEN ${actual_statement_no} LIKE "Spotify%" THEN "Streaming Audio" WHEN ${actual_statement_no} LIKE "iTunes_AppleMusic%" THEN "Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "iTunes_Fraudulent_Plays%" THEN "Fraudulent Stream Plays" WHEN ${actual_statement_no} LIKE "iTunes_Beats%" THEN "Ad-Supported Streaming Radio" WHEN ${actual_statement_no} LIKE "iTunes_Match%" THEN "Cloud" WHEN ${actual_statement_no} LIKE "iTunes_DJMix%" THEN "Subscription Streaming Audio (DJ Mix)" WHEN ${actual_statement_no} LIKE "iTunes_Fitness%" THEN "Subscription Streaming Audio (Fitness)" WHEN ${actual_statement_no} LIKE "iTunes_Radio%" THEN "Non-Interactive Radio" -- WHEN ${actual_statement_no} LIKE "iTunes_%RED%" THEN "Downloads (Early RED Revenue)" WHEN ${actual_statement_no} LIKE "iTunes_%" THEN "Downloads" WHEN ${actual_statement_no} LIKE "YouTube_VOD%" THEN "Video On Demand" WHEN ${actual_statement_no} LIKE "YouTube_Shorts%" THEN "Short-Form UGC Video" WHEN ${actual_statement_no} LIKE "YouTube_ConRes%" THEN "Resolved Ad-Supported Streaming Video" WHEN ${actual_statement_no} LIKE "YouTube_Direct%" THEN "Ad-Supported Direct Ad Sales" WHEN ${actual_statement_no} LIKE "YouTube_Paid%" THEN "Paid Features (Stickers and Superchat)" WHEN ${actual_statement_no} LIKE "YouTube_%VID_FTUN%" THEN "Ad-Supported Streaming Video (Finetunes GMBH account)" WHEN ${actual_statement_no} LIKE "YouTube_%AUD_FTUN%" THEN "Ad-Supported Streaming Video (Finetunes account)" WHEN ${actual_statement_no} LIKE "YouTube_%" THEN "Ad-Supported Streaming Video" WHEN ${actual_statement_no} LIKE "MusicKey_AudioTier%" THEN "Subscription YouTube (Audio Tier)" WHEN ${actual_statement_no} LIKE "MusicKey_%" THEN "Subscription YouTube" WHEN ${actual_statement_no} LIKE "Mondia_Ampya%" THEN "Ampya Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Mondia_Streams%" THEN "Mondia Subscription Streaming Audio" WHEN ${actual_statement_no} LIKE "Mondia_%" THEN "Downloads" WHEN ${actual_statement_no} LIKE "24/7_%PD%7DIG%" THEN "Downloads (Invoice 7-Digital)" WHEN ${actual_statement_no} LIKE "24/7_%PD%JUKE%" THEN "Downloads (Invoice Juke GmbH)" WHEN ${actual_statement_no} LIKE "24/7_%TDC%" THEN "TDC Subcription Streaming Audio" WHEN ${actual_statement_no} LIKE "24/7_Juke%" THEN "Juke Subcription Streaming Audio" WHEN ${actual_statement_no} LIKE "TDC%" THEN "TDC Subcription Streaming Audio (Invoice TDC)" -- CLARO RINGTONES WHEN ${actual_statement_no} LIKE "ClaroMusica_Ringtones%\_MX%" THEN "Ringtones (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica_Ringtones%\_GT%" THEN "Ringtones (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica_Ringtones%\_CO%" THEN "Ringtones (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica_Ringtones%\_PE%" THEN "Ringtones (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica_Ringtones%\_BR%" THEN "Ringtones (Bill to Manuela in BR)" -- CLARO STREAMS WHEN ${actual_statement_no} LIKE "ClaroMusica%\_MX%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_HN%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_NI%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_PA%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_SV%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_GT%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_CR%" THEN "Streams and Downloads (Bill to Veronica in MX)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_CO%" THEN "Streams and Downloads (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_EC%" THEN "Streams and Downloads (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_CL%" THEN "Streams and Downloads (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_PE%" THEN "Streams and Downloads (Bill to Andres in CO)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_DO%" THEN "Streams and Downloads (Bill to Manuela in US)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_BR%" THEN "Streams and Downloads (Bill to Manuela in BR)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_AR%" THEN "Streams and Downloads (Collected by EPSA)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_UY%" THEN "Streams and Downloads (Collected by EPSA)" WHEN ${actual_statement_no} LIKE "ClaroMusica%\_PY%" THEN "Streams and Downloads (Collected by EPSA)" -- International Collections societies: WHEN ${actual_statement_no} LIKE 'Abramus%' THEN 'Abramus' WHEN ${actual_statement_no} LIKE 'Acinpro%' THEN 'Acinpro' WHEN ${actual_statement_no} LIKE 'Agedi%' THEN 'Agedi' WHEN ${actual_statement_no} LIKE 'Capif%' THEN 'Capif' WHEN ${actual_statement_no} LIKE 'Connect%' THEN 'Connect/ReSound' WHEN ${actual_statement_no} LIKE 'CUD%' THEN 'CUD' WHEN ${actual_statement_no} LIKE 'Grammo%' THEN 'Grammo' WHEN ${actual_statement_no} LIKE 'GVL%' THEN 'GVL' WHEN ${actual_statement_no} LIKE 'HKRIA%' THEN 'HKRIA' WHEN ${actual_statement_no} LIKE 'IFPI_IL%' THEN 'IFPI Israel' WHEN ${actual_statement_no} LIKE 'IFPI_SE%' THEN 'IFPI Sweden' WHEN ${actual_statement_no} LIKE 'Intergram%' THEN 'Intergram' WHEN ${actual_statement_no} LIKE 'Kithara%' THEN 'Kithara' WHEN ${actual_statement_no} LIKE 'Laipa%' THEN 'LaIPA' WHEN ${actual_statement_no} LIKE 'Passmusica%' THEN 'Passmusica' WHEN ${actual_statement_no} LIKE 'PPB_via_Sony%' THEN 'CA PPB via Sony' WHEN ${actual_statement_no} LIKE 'PPCA%' THEN 'PPCA' WHEN ${actual_statement_no} LIKE 'PPL%' THEN 'PPL' WHEN ${actual_statement_no} LIKE 'RecordedMusic%' THEN 'Recorded Music' WHEN ${actual_statement_no} LIKE 'SCD%' THEN 'SCD' WHEN ${actual_statement_no} LIKE 'SCF%' THEN 'SCF' WHEN ${actual_statement_no} LIKE 'SCPP%' THEN 'SCPP' WHEN ${actual_statement_no} LIKE 'SGP%' THEN 'SGP' WHEN ${actual_statement_no} LIKE 'Sena%' THEN 'Sena' WHEN ${actual_statement_no} LIKE 'Simim%' THEN 'Simim' WHEN ${actual_statement_no} LIKE 'Slovgram%' THEN 'Slovgram' WHEN ${actual_statement_no} LIKE 'Somexfon%' THEN 'Somexfon' WHEN ${actual_statement_no} LIKE 'Swissperform%' THEN 'Swissperform' WHEN ${actual_statement_no} LIKE 'Zapraf%' THEN 'Zapraf' WHEN ${actual_statement_no} LIKE 'Zavod%' THEN 'Zavod' ELSE "" END ;; type: string description: "Sub-Service information for certain DMSs that offer multiple front-facing options, as well as Collections Society names. Use this in conjunction with Store Name for best results." } dimension: actual_statement_no { view_label: "Statement" label: "Actual Statement Name" type: string sql: ${TABLE}.actual_statement_no ;; } dimension: country_id { type: number sql: ${TABLE}.country_id ;; hidden: yes } dimension: territory { view_label: "Territory" label: "Territory" type: string sql: ${TABLE}.territory ;; } # dimension: continent { # view_label: "Territory" # hidden: yes # label: "Continent" # type: string # sql: ${TABLE}.continent ;; # } # dimension: country_name { # hidden: yes # view_label: "Territory" # label: "Country Name" # type: string # sql: ${TABLE}.country_name ;; # } dimension: customer_master_id { view_label: "Store" label: "Sub-Store ID" type: number sql: ${TABLE}.customer_master_id ;; } dimension: customer_master_master_id { view_label: "Store" label: "Store ID" type: number # hidden: true sql: ${TABLE}.customer_master_master_id ;; } dimension: customer_name { view_label: "Store" label: "Store Name (When Ingested)" type: string sql: ${TABLE}.customer_name ;; description: "The Store Name on the date the data was initially injested; may not match current OA/Workstation name." } dimension_group: date { view_label: "Date" label: "Activity" type: time timeframes: [month, quarter, year] convert_tz: no sql: ${TABLE}.date ;; description: "When the sales/streams occurred." } dimension_group: date_created { view_label: "Date" label: "Created" type: time timeframes: [date, month, quarter, year, time] sql: ${TABLE}.date_created ;; description: "The date the report containing this data was initially ingested into the database." } dimension_group: date_updated { view_label: "Date" label: "Updated" type: time timeframes: [date, month, quarter, year, time] sql: ${TABLE}.date_updated ;; description: "The date any changes were made in the reject split, re-deliveries, or re-ingestion of a file." } dimension: dms_name { view_label: "Store" label: "Sub-Store Name (When Ingested)" type: string sql: ${TABLE}.dms_name ;; description: "The Sub-Store Name on the date the data was initially injested; may not match current OA/Workstation name." } measure: red_qty { view_label: "Units" label: "RED Units" type: sum sql: ${TABLE}.red_qty ;; } dimension: red_qty_num { view_label: "Units" hidden: yes label: "RED Units" type: number sql: ${TABLE}.red_qty ;; } measure: red_revenue { view_label: "Revenue" label: "RED Revenue" type: sum sql: ${TABLE}.red_revenue ;; value_format: "$#,##0.00" } dimension: red_revenue_number { view_label: "Revenue" hidden: yes label: "RED Revenue" type: number sql: ${TABLE}.red_revenue ;; value_format: "$#,##0.00" } measure: redelivered_qty { view_label: "Units" label: "Redelivered Units" type: sum sql: ${TABLE}.redelivered_qty ;; } measure: redelivered_revenue { view_label: "Revenue" label: "Redelivered Revenue" type: sum sql: ${TABLE}.redelivered_revenue ;; value_format: "$#,##0.00" } measure: rejected_qty { view_label: "Units" label: "Rejected Units" type: sum sql: ${TABLE}.rejected_qty ;; } dimension: rejected_qty_num { view_label: "Units" hidden: yes label: "Rejected Units Number" type: number sql: ${TABLE}.rejected_qty ;; } measure: rejected_revenue { view_label: "Revenue" label: "Rejected Revenue" type: sum sql: ${TABLE}.rejected_revenue ;; value_format: "$#,##0.00" } measure: rejected_revenue_percentage { view_label: "Revenue" label: "Rejected Percentage (Revenue)" type: number sql: ${rejected_revenue}/${total_revenue} ;; value_format: "#,##0.0000%" } dimension: rejected_revenue_number { view_label: "Revenue" hidden: yes label: "Rejected Revenue Number" type: number sql: ${TABLE}.rejected_revenue ;; value_format: "$#,##0.00" } measure: rows_rejected { view_label: "Statement" label: "Rows Rejected" type: sum sql: ${TABLE}.rows_rejected ;; } measure: sc_qty { view_label: "Units" label: "Supply Chain Units" type: sum sql: ${TABLE}.sc_qty ;; } measure: sc_revenue { view_label: "Revenue" label: "Supply Chain Revenue" type: sum sql: ${TABLE}.sc_revenue ;; value_format: "$#,##0.00" } measure: total_qty { view_label: "Units" label: "Total Units" type: sum sql: ${TABLE}.total_qty ;; } dimension: total_qty_num { view_label: "Units" hidden: yes label: "Total Units" type: number sql: ${TABLE}.total_qty ;; } measure: reject_percentage_qty { view_label: "Units" label: "Reject Percentage (QTY)" type: number sql: ${rejected_qty}/${total_qty} ;; value_format: "0.000000" } measure: total_revenue { view_label: "Revenue" label: "Total Revenue" type: sum sql: ${TABLE}.total_revenue ;; value_format: "$#,##0.00" } dimension: total_revenue_number { hidden: yes view_label: "Revenue" label: "Total Revenue Number" type: number sql: ${TABLE}.total_revenue ;; value_format: "$#,##0.00" } measure: non_red { view_label: "Revenue" label: "Non-RED Revenue" type: sum sql: ${total_revenue_number}-${red_revenue_number} ;; value_format: "$#,##0.00" } measure: non_red_units { view_label: "Units" label: "Non-RED Units" type: sum sql: ${total_qty_num}-${red_qty_num} ;; value_format: "$#,##0.00" } measure: total_rows { view_label: "Statement" label: "Total Rows" type: sum sql: ${TABLE}.total_rows ;; } dimension: trans_type { view_label: "Transaction" label: "Type" hidden: yes type: string sql: ${TABLE}.trans_type ;; } }