Is there a more elegant way to calculate a percentage based on a pivoted and filtered bucket?

[Rwb]

I’m trying to create a simple chart of 3 lines where the x-axis is a date (grouped by week) and the y-axis is a percentage.

I’m trying to analyze our recruiting efforts. The 3 lines are for different departments, and the percentage is (roughly) “# of new hires divided by # of offers made”.

Additionally, I’d like to smooth the lines by looking at the data in a rolling-4-week basis.

So far, my approach feels so clunky that I doubt it’s the appropriate Looker way.

For each department (and I feel lucky that I’m only looking at 3), it seems like I need to create 3 Table Calculations:

1. numerator (named corp_hired_rolling_4wk): sum(offset_list(pivot_where((${stg_departments.department_bucket}="Corp" AND ${applications.status} = "hired"),${applications.count}),-3,4))

2. denominator (named corp_offered_rolling_4wk):

  offset_list(
    pivot_where(
      (
        ${stg_departments.department_bucket}="Corp" AND ${applications.status} = "hired"
      ),${applications.count}
    ),-3,4
  )
)
+
sum(
 offset_list(
   pivot_where(
      (
        ${stg_departments.department_bucket}="Corp" AND ${applications.status} = "rejected"),${applications.count}
    ),-3,4
 )
)

3. percentage: ${corp_hired_rolling_4wk}/${corp_offered_rolling_4wk}

Is there a more correct or elegant approach?

(It doesn’t “feel right” to be creating so many Table Calculations, especially where I need to put hard-coded department names in quotes multiple times and then make equivalent Table Calculations for each department.)

Thank you so much for any tips.