TL;DR: imagine a column with company names and a second column with a numerical value for each; we devised an easy way to single out one of the companies and averaging all others into a “Competitors” category. E.g., ABC Corp vs the rest:
What for?
Some context: at Handshake we operate a jobs marketplace to help millions of college students find meaningful careers. We frequently get benchmarking requests from our customers (employers and universities) asking how they’re doing compared to others. A couple issues with this: (1) some competitor info is too sensitive to disclose, and (2) it can be difficult to grasp on a simple chart with many other competitors due to variation (competitor’s sizes, industry, location, etc. influence activity numbers).
Good news, bad news
Averaging competitor activity should both mask competitor names when with aggregation and provide a single data point that’s easy to grasp and benchmark against. However, averaging using measures acts on all rows, you can’t tell looker to aggregate groups of rows. You could join the “companies” view twice to the explore, one for the main company and the second for competitors and apply filters accordingly. Or, make two charts --one for customer and other for competitors-- and lay them side to side in a dashboard, but the y-axis scale will not match between the two.
The better way: templated filters
The cleaner solution was to have a dimension do the splitting/grouping of companies and letting the measure do it’s job. First, we created a new dimension with a CASE statement that separated the company we wanted from the rest:
dimension: company_vs_competitor {
type: string
sql: CASE
WHEN ${company_name} = 'ABC Corp' THEN ${company_name}
ELSE 'Competitors'
END ;;
}
This works great for ABC Corp, but we want this to work for any company. So we create a filter in the view and use it as a templated filter in the case statement so that the string condition can be manipulated from the explore via the filter:
filter: company_for_comparison {
type: string
}
dimension: company_vs_competitor {
type: string
sql: CASE
WHEN {% condition company_for_comparison %} ${company_name} {% endcondition %} THEN ${company_name}
ELSE 'Competitors'
END ;;
}
Next, filter all employer names that will go into your comparison including the interested employer (i.e. ABC Corp). Pop in the new company_for_comparison filter and add the employer you want to single out. Use your new company_vs_competitor as a pivot and throw in an measure to average some activity related to the employer. Hit that Run button! 🎉 🎊 🎆
