views/revenue/to_date_fields.view.lkml

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