Looker measure to average over last 3 years

Question

I am trying to create a measure (LookML) that gives the average of funding over a three year window.

A sample of my data looks like this:

year	person	funding
2022	Amy	20,000
2022	Josh	15,000
2021	Amy	23,000
2021	Josh	11,000
2020	Amy	29,000
2020	Josh	10,000

I'd like the measure to produce resulting data like this:

person	3 year avg. funding
Amy	24,000
Josh	12,000

The user would be able to pick the 3 year window using a filter, so the real data is between 1975-2023. I've tried this (below) but it's not worked.

  measure: average_funding {
    type: average
    label: "Average funding"
    sql: CASE WHEN ${year} >= CURRENT_DATE() -3 THEN ${funding} ELSE NULL END;;
  }

Answer 1

In case, i assume your table is called my_table, and the LookML view is named my_view. The derived table selects all columns from my_table.

The person dimension is defined based on the person column in the table.

The average_funding measure uses the average type and refers to the funding column in the table. The measure is filtered using the filters parameter, which applies a date range filter on the year dimension.

The date range filter is defined using Liquid syntax and the date.add_years function. It calculates the three-year window dynamically by subtracting 3 years from the current date (date.add_years(-3)) and including all years up to the current date (date.add_years(0)).

By setting up the measure and filter as shown above, you should be able to calculate the three-year average funding based on the user's selected filter window.

For example LookML :

view: my_view {
  derived_table: {
    sql: SELECT * FROM my_table;;
  }

  dimension: person {
    type: string
    sql: ${TABLE}.person ;;
  }

  measure: average_funding {
    type: average
    label: "3-Year Average Funding"
    sql: ${funding} ;;
    filters: {
      field: year
      view: my_view
      dimension: year
      operators: [in]
      values: ["{% condition year %} {{ date.add_years(-3) }} {% endcondition %}" TO "{{ date.add_years(0) }}"]
    }
  }
}