views/revenue/comparisons_from_revenue.view.lkml

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