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.

[Connor_Sparkman]

In situations that require more than one pivot, I tend to push one of them down into Custom Measures to clean up the need for complex table calc logic.

In your case, I’d choose the “Application Status” field, since the field values are more likely to remain constant (i.e. “hired” and “rejected” will [hopefully] remain unchanged as status values):

(you can duplicate the measure and update the logic for “Rejected”)

After you have your custom measures for “Hired” and “Rejected”, you can create the table calcs you want. May be good to keep the “Offer Conversion Rate” calc separate from the “Rolling 4-week” to keep the logic clean, but that’s a personal preference. Either way, the table calc logic should be much simpler now as you can just reference the two custom measures:

The final look would appear like this:

Hopefully this is a little cleaner of a solution. At a minimum, it is scalable as you shouldn’t need to update anything if/when you want to pivot on more departments.

As a side note, if you use (or anticipate using) the “Hired” & “Rejected” measures often, I’d suggest building out LookML measures for them. The logic would all remain the same for a report like this, but save you from having to create the two Custom Measures every time.
Hope this helps!

[Rwb]

Thank you @Connor_Sparkman! I’m so glad I asked. This definitely feels cleaner.