Additional views Change-Id: Id141c275a001528e7162d0014d519f8c06a2cbb5
diff --git a/explores/revenue.view.lkml b/explores/revenue.view.lkml index d77b2e8..cdad4cf 100644 --- a/explores/revenue.view.lkml +++ b/explores/revenue.view.lkml
@@ -11,4 +11,9 @@ , revenue.sub_region: "-NULL" , revenue.service_family: "CLOUD" ] - }} + } + join: max_date { + type: cross + relationship: one_to_one + } + }
diff --git a/views/revenue/comparisons_from_revenue.view.lkml b/views/revenue/comparisons_from_revenue.view.lkml new file mode 100644 index 0000000..3a18233 --- /dev/null +++ b/views/revenue/comparisons_from_revenue.view.lkml
@@ -0,0 +1,102 @@ +# https://lookercs-internal.git.corp.google.com/cloud_sales_bi/+/refs/heads/master/views/revenue/comparisons_from_revenue.view.lkml + +view: comparisons_from_revenue { + extension: required + ########## DIMENSIONS ############ + dimension: comparison_usage_day_of_period { + view_label: "Revenue Comparisons" + group_label: "Comparison Usage Date" + type: number + sql: + CASE + WHEN ${comparison_period} IS NULL + THEN NULL + WHEN ${comparison_period} = "Current Period" + THEN DATE_DIFF(${usage_raw}, {% date_start comparison_date_range %}, DAY) + ELSE + DATE_DIFF( + ${usage_raw} + , DATE_ADD( + {% date_start comparison_date_range %} + , INTERVAL CAST(-1 * ${comparison_period_num} AS INT64) {% parameter comparison_type %}) + , DAY) + END ;; + } + dimension_group: comparison_usage { + description: "Use this as an X-axis for revenue comparisons" + view_label: "Revenue Comparisons" + type: time + datatype: date + timeframes: [raw, date, week, month, quarter, year] + sql: DATE_ADD({% date_start comparison_date_range %}, INTERVAL ${comparison_usage_day_of_period} DAY) ;; + convert_tz: no + } + dimension: comparison_period { + suggestions: ["Current Period", "Prior Period"] + view_label: "Revenue Comparisons" + description: "Label used to differentiate between Current and Prior periods for comparisons." + type: string + sql: CASE + WHEN ${comparison_period_num} IS NULL + THEN NULL + WHEN ${comparison_period_num} = 0 + THEN "Current Period" + WHEN ${comparison_period_num} = 1 + THEN "Prior Period" + ELSE CONCAT(CAST(${comparison_period_num} AS STRING), ' Periods Ago') + END;; + } + dimension: comparison_period_num { + hidden: yes + type: number + description: "Calculates how many periods ago this date is from our comparison (and removed dates outside of our range)." + sql: CASE + WHEN ${usage_raw} > {% date_end comparison_date_range %} + THEN NULL + WHEN ${usage_raw} BETWEEN {% date_start comparison_date_range %} AND {% date_end comparison_date_range %} + THEN 0 + WHEN ${usage_raw} BETWEEN DATE_ADD({% date_start comparison_date_range %}, INTERVAL -1 {% parameter comparison_type %}) + AND DATE_ADD({% date_end comparison_date_range %}, INTERVAL -1 {% parameter comparison_type %}) + THEN 1 + WHEN ${usage_raw} BETWEEN DATE_ADD({% date_start comparison_date_range %}, INTERVAL -2 {% parameter comparison_type %}) + AND DATE_ADD({% date_end comparison_date_range %}, INTERVAL -2 {% parameter comparison_type %}) + THEN 2 + WHEN ${usage_raw} BETWEEN DATE_ADD({% date_start comparison_date_range %}, INTERVAL -3 {% parameter comparison_type %}) + AND DATE_ADD({% date_end comparison_date_range %}, INTERVAL -3 {% parameter comparison_type %}) + THEN 3 + WHEN ${usage_raw} BETWEEN DATE_ADD({% date_start comparison_date_range %}, INTERVAL -4 {% parameter comparison_type %}) + AND DATE_ADD({% date_end comparison_date_range %}, INTERVAL -4 {% parameter comparison_type %}) + THEN 4 + WHEN ${usage_raw} BETWEEN DATE_ADD({% date_start comparison_date_range %}, INTERVAL -5 {% parameter comparison_type %}) + AND DATE_ADD({% date_end comparison_date_range %}, INTERVAL -5 {% parameter comparison_type %}) + THEN 5 + ELSE NULL + END ;; + } + ########## PARAMETERS ############ + parameter: comparison_type { + view_label: "Revenue Comparisons" + description: "Sets the comparison type for Revenue Evolution" + default_value: "YEAR" + type: unquoted + allowed_value: { + label: "Year-over-Year" + value: "YEAR" + } + allowed_value: { + label: "Quarter-over-Quarter" + value: "QUARTER" + } + allowed_value: { + label: "Month-over-Month" + value: "MONTH" + } + } + filter: comparison_date_range { + view_label: "Revenue Comparisons" + description: "Sets the period for examining Revenue Evolution" + default_value: "365 day" + type: date + datatype: date + } +}
diff --git a/views/revenue/revenue.view.lkml b/views/revenue/revenue.view.lkml index cba9b62..ccf848d 100644 --- a/views/revenue/revenue.view.lkml +++ b/views/revenue/revenue.view.lkml
@@ -1,80 +1,13 @@ # Approximates https://lookercs-internal.git.corp.google.com/cloud_sales_bi/+/refs/heads/master/views/revenue/revenue.view.lkml include: "account_from_account_revenue.view.lkml" +include: "comparisons_from_revenue.view.lkml" include: "revenue_from_revenue.view.lkml" +include: "rolling_window.view.lkml" +include: "to_date_fields.view.lkml" view: revenue { - extends: [account_from_revenue, revenue_from_revenue] + extends: [account_from_revenue, comparisons_from_revenue, revenue_from_revenue, to_date_fields, rolling_window] sql_table_name: `concord-test.stagingservice_gcc.gcc_revenue_partitioned_view`;; - # dimension: billing_account_id { - # view_label: "Account" - # group_label: "Identifiers" - # description: "Billing account ID" - # sql: ${TABLE}.billing_account_id ;; - # } - - # dimension: sfdc_account_id { - # alias: [account_meta.sfdc_account_id] - # label: "SFDC Account ID" - # view_label: "Account" - # description: "Salesforce account of the opportunity" - # type: string - # sql: ${TABLE}.sfdc_account_id ;; - # link: { - # label: "Account in Vector" - # url: "https://vector.lightning.force.com/lightning/r/Account/{{ value }}/view" - # } - # } - - # dimension: currency_code { - # view_label: "Revenue" - # description: "Three letter currency code (e.g. USD) for local billing amount." - # sql: ${TABLE}.currency_code ;; - # } - - # dimension_group: invoice_month_start { - # type: time - # timeframes: [raw, date, week, month, quarter, year] - # view_label: "Revenue" - # sql: ${TABLE}.invoice_month_start ;; - # datatype: date - # convert_tz: no - # } - - # dimension: product_family_l1 { - # label: "Product Family (L1)" - # group_label: "Finance Product Hierarchy" - # view_label: "Product" - # description: "Examples: Google Workspace, GCP, Maps, PSO" - # sql: ${TABLE}.product_group ;; - # drill_fields: [product_group_l2] - # } - # dimension: product_group_l2 { - # label: "Product Group (L2)" - # group_label: "Finance Product Hierarchy" - # view_label: "Product" - # description: "Examples: Marketplace Services, Compute, Support, Databases, etc." - # sql: ${TABLE}.product_group_l2 ;; - # drill_fields: [logical_product_l3] - # } - # dimension: logical_product_l3 { - # label: "Logical Product (L3)" - # group_label: "Finance Product Hierarchy" - # view_label: "Product" - # description: "Examples: Persistent Disk, VM Image Storage, GCE" - # sql: ${TABLE}.logical_product_l3 ;; - # } - - # dimension: usd_revenue_gross { - # # hidden: yes - # view_label: "Revenue" - # description: "Monetised value for resource consumption, before application of any credits, reported in USD($) using a monthly Ceilometer exchange rate. (Generally a positive number)" - # sql: ${TABLE}.usd_revenue_gross ;; - # } - # dimension: usd_revenue_gross_cn { - # hidden: yes - # view_label: "Revenue" - # sql: ${TABLE}.usd_revenue_gross_cn ;; - # } }
diff --git a/views/revenue/rolling_window.view.lkml b/views/revenue/rolling_window.view.lkml new file mode 100644 index 0000000..83a85e9 --- /dev/null +++ b/views/revenue/rolling_window.view.lkml
@@ -0,0 +1,130 @@ +# https://lookercs-internal.git.corp.google.com/cloud_sales_bi/+/refs/heads/master/views/revenue/rolling_window.view.lkml +view: rolling_window { + extension: required + ########## DIMENSIONS ############ + dimension: rolling_window { + view_label: "Revenue Comparisons" + order_by_field: rolling_window_num + description: "Grouping for rolling windows (use window_size parameter to change from default 28)" + type: string + sql: CONCAT( + ${rolling_window_start_month_name} + , ' ' + , ${rolling_window_start_day_of_month} + , ', ' + , ${rolling_window_start_year} + , ' --> ' + , ${rolling_window_end_month_name} + , ' ' + , ${rolling_window_end_day_of_month} + , ', ' + , ${rolling_window_end_year} + ) ;; + } + dimension_group: rolling_window_end { + description: "Start date for rolling windows (use window_size parameter to change from default 28)" + hidden: yes + type: time + timeframes: [raw, day_of_month, month_name, year] + convert_tz: no + sql: DATE_ADD( + ${max_date.max_date_minus_2_raw} + , INTERVAL CAST(-1 * ({% parameter window_size %} + 1) * ${rolling_window_num} AS INT64) DAY + ) ;; + } + dimension: rolling_window_num { + description: "Grouping for rolling windows (use window_size parameter to change from default 28)" + hidden: yes + type: number + sql: CASE + WHEN ${usage_raw} > ${max_date.max_date_minus_2_raw} + THEN NULL + ELSE FLOOR(DATE_DIFF(${max_date.max_date_minus_2_raw}, ${usage_raw}, DAY) / {% parameter window_size %}) + END ;; + } + dimension_group: rolling_window_start { + description: "End date for rolling windows (use window_size parameter to change from default 28)" + hidden: yes + type: time + timeframes: [raw, day_of_month, month_name, year] + convert_tz: no + sql: DATE_ADD( + ${max_date.max_date_minus_2_raw} + , INTERVAL CAST((-1 * {% parameter window_size %}) + (-1 * ({% parameter window_size %} + 1)) * ${rolling_window_num} AS INT64) DAY + ) ;; + } +########## MEASURES ############### + measure: rolling_window_current_window { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Sales)" + label: "Current Window Revenue (Sales)" + description: "Revenue during most recent window (use with filter Window Size)" + sql: ${usd_revenue_blended} ;; + type: sum + filters: [rolling_window_num: "0"] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } + measure: rolling_window_current_window_gross { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Gross)" + label: "Current Window Revenue (Gross)" + description: "Revenue during most recent window (use with filter Window Size)" + sql: ${usd_revenue_gross} ;; + type: sum + filters: [rolling_window_num: "0"] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } + measure: rolling_window_prior_window { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Sales)" + label: "Prior Window Revenue (Sales)" + description: "Revenue during previous window (use with filter Window Size)" + sql: ${usd_revenue_blended} ;; + type: sum + filters: [rolling_window_num: "1"] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } + measure: rolling_window_prior_window_gross { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Gross)" + label: "Prior Window Revenue (Gross)" + description: "Revenue during previous window (use with filter Window Size)" + sql: ${usd_revenue_gross} ;; + type: sum + filters: [rolling_window_num: "1"] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } + measure: rolling_window_window_variance { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Sales)" + label: "Window Variance (Sales)" + type: number + description: "Difference between current and prior window revenue (use with filter Window Size)" + sql: ${rolling_window_current_window} - ${rolling_window_prior_window} ;; + drill_fields: [billing_account_id, rolling_window_window_variance] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } + measure: rolling_window_window_variance_gross { + view_label: "Revenue Comparisons" + group_label: "Rolling Window (Gross)" + label: "Window Variance (Gross)" + type: number + description: "Difference between current and prior window revenue for Revenue (Gross) (use with filter Window Size)" + sql: ${rolling_window_current_window_gross} - ${rolling_window_prior_window_gross} ;; + drill_fields: [billing_account_id, rolling_window_window_variance_gross] + # html: @{custom_dollar_formatting} ;; + value_format_name: usd_0 + } +########## PARAMETERS ############### + parameter: window_size { + view_label: "Revenue Comparisons" + description: "Window for comparison periods (rolling_window). Default is 28 days." + type: number + default_value: "28" + } +}
diff --git a/views/revenue/to_date_fields.view.lkml b/views/revenue/to_date_fields.view.lkml new file mode 100644 index 0000000..d7b8fe9 --- /dev/null +++ b/views/revenue/to_date_fields.view.lkml
@@ -0,0 +1,67 @@ +# https://lookercs-internal.git.corp.google.com/cloud_sales_bi/+/refs/heads/master/views/revenue/to_date_fields.view.lkml + +view: to_date_fields { + extension: required + ########## DIMENSIONS ############ + dimension: day_of_quarter { + hidden: yes + description: "Days from start of quarter" + sql: DATE_DIFF(${usage_raw}, DATE_TRUNC(${usage_raw}, QUARTER), DAY) ;; + type: number + } + dimension: month_to_date { + view_label: "Revenue Comparisons" + label: "Month to Date Groupings" + type: string + description: "Grouping to get the portion of a month that is equivalent to \"Month to Date\". Example: \"January 1 to 16\"" + sql: + CASE + WHEN ${usage_raw} > ${max_date.max_date_minus_2_raw} + THEN NULL + WHEN ${usage_day_of_month} <= ${max_date.max_date_minus_2_day_of_month} + THEN CONCAT( + ${usage_month_name} + , ' 1 to ' + , ${max_date.max_date_minus_2_day_of_month} + , ', ' + , ${usage_year} + ) + ELSE NULL + END;; + order_by_field: month_to_date_order + } + dimension: month_to_date_order { + hidden: yes + type: string + description: "Used for ordering the month_to_date dimension" + sql: IF(${usage_day_of_month} <= ${max_date.max_date_minus_2_day_of_month}, ${usage_month}, NULL);; + } + dimension: quarter_to_date { + view_label: "Revenue Comparisons" + label: "Quarter to Date Groupings" + type: string + description: "Grouping to get the portion of each quarter that is equivalent to \"Quarter to Date\". Example: \"April 1 to May 16, 2018\" and \"January 1 to February 16, 2018\"" + sql: IF(${day_of_quarter} <= ${max_date.max_date_minus_2_day_of_quarter}, ${usage_quarter}, NULL);; + } + dimension: year_to_date { + view_label: "Revenue Comparisons" + label: "Year to Date Groupings" + type: string + description: "Grouping to get the portion of a year that is equivalent to \"Year to Date\". Example: \"January 1 to March 16, 2018\"" + sql: + CASE + WHEN ${usage_day_of_year} <= ${max_date.max_date_minus_2_day_of_year} + THEN CONCAT( + 'January 1, ' + , ${usage_year} + , ' to ' + , ${max_date.max_date_minus_2_month_name} + , ' ' + , ${max_date.max_date_minus_2_day_of_month} + , ', ' + , ${usage_year} + ) + ELSE NULL + END;; + } +}