Here’s the situation I’m in:
Let’s say I’m trying to create a visualization for hiring positions for a team. The dataset I’m working with has each observation as an interview. So the columns of the dataset would be something like: Name, Gender, Location (State), Education Level, Interview Round. Interview Round is one of the following 5 values: First Round, Second Round, Third Round, Final Round, Hired.
I have successfully created the visualization as a bar chart by just summing the values for each of the Interview Round. Now, I’m looking to create a measure of ratio for each Interview Round. For example, if there were 100 candidates who got a first round interview and 40 who got a second round interview, I want to be able to hover over the bar for Second Round Interview and see 40%. I’m trying to do this through a Beast Mode followed by a Tooltip, however, getting this completed in Beast Mode is where I’ve found my issue.
Here’s what I have so far as the first step, but it’s not working:
CASE WHEN `Interview Round` = ‘First Round’ THEN 1
WHEN `Interview Round` = ‘Second Round’ THEN SUM(CASE WHEN `Interview Round` = ‘Second Round’)/SUM(CASE WHEN `Interview Round` = ‘First Round’)
WHEN `Interview Round` = ‘Third Round’ THEN SUM(CASE WHEN `Interview Round` = ‘Third Round’)/SUM(CASE WHEN `Interview Round` = ‘Second Round’)
…
END
The reason it’s not working is that the observations with `Interview Round` = ‘First Round’ are not included after the first WHEN statement. So my question is this: how can I assign a specific calculation to observations with `Interview Round` = ‘Second Round’ while still including observations with `Interview Round` = ‘First Round’ in the calculation? (In Beast Mode)
The reason I find this necessary to do in Beast Mode is I need this ratio to change every time I filter based on things like ‘Gender’, ‘Education level’, etc.
