At Handshake we operate a jobs marketplace to help millions of college students find jobs. Since activity on the platform is closely tied to activity at Universities it only makes sense that we plot student activity trends over time by school-year rather than calendar-year:
This style of date scale modification allows us to have a better view of the school year and helps us compare them year-on-year for better visibility. Below is the lookml for the custom dates – you can modify the date cutoffs and month numbers to adapt where the cutoff happens. We use Snowflake as our data warehouse which explains the syntax in sql fields:
dimension: school_year_month_period {
group_label: "Period Analysis"
type: string
sql: ( case
when MONTH(${TABLE}.ACTIVITY_DATE) between 5 and 7 then (MONTH(${TABLE}.ACTIVITY_DATE) + 5)::text
when MONTH(${TABLE}.ACTIVITY_DATE) between 1 and 4 then '0' || (MONTH(${TABLE}.ACTIVITY_DATE) + 5)::text
else '0' || (MONTH(${TABLE}.ACTIVITY_DATE) - 7)::text
end) || ' - ' || TO_CHAR(${TABLE}.ACTIVITY_DATE, 'mon') ;;
description: "month of the year starting from August"
}
dimension: school_year_period {
group_label: "Period Analysis"
type: string
sql: CASE
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2013-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2014-08-01') THEN '2014'
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2014-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2015-08-01') THEN '2015'
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2015-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2016-08-01') THEN '2016'
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2016-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2017-08-01') THEN '2017'
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2017-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2018-08-01') THEN '2018'
WHEN ${TABLE}.ACTIVITY_DATE >= TO_DATE('2018-08-01') AND ${TABLE}.ACTIVITY_DATE < TO_DATE('2019-08-01') THEN '2019'
WHEN true THEN 'Other'
END;;
description: "school year: from August 1st to July 31st of following year"
}
After the new dimensions become available you can set the month dimension for your rows and pivot on the year to create a year-on-year comparison of every month:
