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