Visualizing Incomplete Time Periods


(Eric Hutcheson) #1

Sometimes users don’t want to see the little bar representing the current month or quarter that may have just started.


In certain cases it could be seen as misleading or some users may wonder why it is so small compared to the other time periods. They want to see what that time period should look like when it is complete so they understand how they are pacing in the current time period.

This can be accomplished using forecasting software or predictive analytics, which provide the most sophisticated and potentially accurate way of predicting what will happen in the future. But when you don’t have these tools at your disposal, some simple LookML logic can help you with this.

For example, I can take the Total Sales so far this month and divide it by how many days have passed in the month to get a “Average Daily Sales” number for the month. I can then multiply this by the number of days in the full month (28, 29, 30, or 31) to get to a loosely forecasted number.

  ## Derive Today's Date so we can use it in our calculation
  dimension_group: today {
    type: time
    hidden: yes
    timeframes: [day_of_month, month, month_num, date, raw]
    sql: current_date ;;
  }
  
  ## Derive how many days are in each month to use in our calculation
  dimension: days_in_month {
    hidden: yes
    type: number
    sql:  CASE 
          WHEN ${today_month_num} IN (4,6,9,11) THEN 30
          WHEN ${today_month_num} = 2 THEN 28
          ELSE 31
          END ;;
  }

  dimension: sale_price {
    hidden: yes
    type: number
    sql: ${TABLE}.sale_price ;;
  }

  measure: total_sales {
    type: sum
    value_format_name: "usd_0"
    sql: ${sale_price} ;;
  }

  # During the current month, divide total sales so far by the number of days that have passed and 
  # multiply this by the number of days in the month
  # Optional: Subtract the total sales so far, so that this number is strictly the forcasted component

  measure: total_sales_fcst_this_month {
    required_fields: [date_month]
    label: "Sales Forecast This Month"
    type: number
    value_format_name: "usd_0"
    sql: case when ${date_month} = ${today_month}
         then ((sum(${sale_price}) / max(${today_day_of_month})) * ${days_in_month}) - sum(${sale_price})
         else null
         end ;;
  }

To do this at the quarterly-level, I’ll change my approach slightly because we don’t have a day_of_quarter timeframe in Looker, like we had with day_of_month

 dimension_group: first_day_of_qtr {
    hidden: yes
    type: time
    timeframes: [raw]
    sql: SELECT MIN(${date_raw})
         FROM public.order_items
         WHERE ${today_quarter} = ${date_quarter};;
  }
  
  dimension_group: current_day_of_qtr {
    hidden: yes
    type: time
    timeframes: [raw]
    sql: SELECT MAX(${date_raw})
         FROM public.order_items
         WHERE ${today_quarter} = ${date_quarter};;
  }
  
  dimension: days_passed_in_qtr {
    hidden: yes
    type: number
    ## redshift/postgres example
    sql:  DATEDIFF('day', ${first_day_of_qtr_raw}, ${current_day_of_qtr_raw}) ;;
  }
  
  measure: total_sales_fcst_this_qtr {
    required_fields: [date_quarter]
    label: "Sales Forecast This Quarter"
    type: number
    value_format_name: "usd_0"
    sql: case when ${date_quarter} = ${today_quarter}
         then ((sum(${sale_price}) / ${days_passed_in_qtr}) * 91.25) - sum(${sale_price})
         else null
         end ;;
  }