views/revenue/rolling_window.view.lkml

# 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"
  }
}