Looker 4/4/5 Calendar PDT implementation

445_fiscal_calendar.view.lkml

view: fiscal_calendar {
  # Calculates the 4/4/5 fiscal calendar for all dates 2010-2050
  # Expected query duration <1 s

  derived_table: {
    interval_trigger: "720 hours"
    indexes: ["calendar_date"]
    sql:
      select
        calendar.date as calendar_date,
        (case
          -- When we're within a week of the end of the year
          when
            datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
            and dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) < 372
            then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 364
          when
            datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
            then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 371
          else dayofyear(calendar.date) + weekday(makedate(calendar.date, 1))
        end) as fiscal_day,
        (ceiling(
          (case
            -- When we're within a week of the end of the year
            when
              datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
              and dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) < 372
              then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 364
            when
              datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
              then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 371
            else dayofyear(calendar.date) + weekday(makedate(calendar.date, 1))
          end) / 7
        )) as fiscal_week,
        (case
          when (
            case
              -- When we're within a week of the end of the year
              when
                datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
                and dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) < 372
                then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 364
              when
                datediff((makedate(calendar.date, 1) + interval 1 year), calendar.date) <= weekday(makedate(calendar.date, 1) + interval 1 year)
                then dayofyear(calendar.date) + weekday(makedate(calendar.date, 1)) - 371
              else dayofyear(calendar.date) + weekday(makedate(calendar.date, 1))
            end
          ) < dayofyear(calendar.date) then year(calendar.date) + 1
          else year(calendar.date)
        end) as fiscal_year

      from
      (
        select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as date from
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
         (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
      ) calendar

      where
        calendar.date between '2010-01-01' and '2050-01-01'
    ;;
  }

  dimension: calendar_date {
    type: date
    primary_key: yes
    sql: ${TABLE}.calendar_date ;;
  }

  dimension: fiscal_day {
    type: number
    value_format: "\"D\"0"
    sql: ${TABLE}.fiscal_day ;;
  }
  
  dimension: fiscal_year {
    type: number
    value_format: "0"
    sql: ${TABLE}.fiscal_year ;;
  }

  dimension: fiscal_week {
    type: number
    value_format: "\"W\"0"
    sql: ${TABLE}.fiscal_week ;;
  }
  
  dimension_group: fiscal_week_start {
    group_label: "Fiscal Week Start"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.calendar_date - interval weekday(${TABLE}.calendar_date) day ;;
  }
  
  dimension_group: fiscal_week_end {
    group_label: "Fiscal Week End"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.calendar_date + interval (6 - weekday(${TABLE}.calendar_date)) day ;;
  }

  dimension: fiscal_month {
    type: number
    value_format: "\"M\"0"
    sql:
      case
        when ${fiscal_week} between 1 and 4 then 1
        when ${fiscal_week} between 5 and 8 then 2
        when ${fiscal_week} between 9 and 13 then 3
        when ${fiscal_week} between 14 and 17 then 4
        when ${fiscal_week} between 18 and 21 then 5
        when ${fiscal_week} between 22 and 26 then 6
        when ${fiscal_week} between 27 and 30 then 7
        when ${fiscal_week} between 31 and 34 then 8
        when ${fiscal_week} between 35 and 39 then 9
        when ${fiscal_week} between 40 and 43 then 10
        when ${fiscal_week} between 44 and 47 then 11
        else 12
      end
    ;;
  }

  dimension: fiscal_month_name {
    type: date_month_name
    sql: makedate(${fiscal_year}, 1) + interval (${fiscal_month} - 1) month ;;
  }

  dimension: fiscal_quarter {
    type: number
    value_format: "\"Q\"0"
    sql: ceiling(${fiscal_month} / 3);;
  }
}