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;;
+ }
+}
