Waterfall/Retention Ratio in Beast Mode

JasonKerr

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.

DashboardDude

Hi @JasonKerr ,

I think I need to see it to do more, but your SQL formula looks like it's missing something. So when you write Case when statements you have to have the word "end" at the....end. Here's an example of what is written:

SUM(CASE WHEN `Interview Round` = ‘Second Round’)/SUM(CASE WHEN `Interview Round` = ‘First Round’)

Essentially I think you're trying to do a nested formula within a formula. I'm not sure of the logic you're trying to write so if you maybe give an example I can try to help.

JasonKerr

@DashboardDude. I appreciate you pointing out that mistake, that was not what was in the beast mode, but just a mistake when typing it into my question.

My case statement was:

CASE WHEN `Interview Round` = ‘First Round’ THEN 1

WHEN `Interview Round` = ‘Second Round’ THEN SUM(CASE WHEN `Interview Round` = ‘Second Round’ THEN 1 END)/SUM(CASE WHEN `Interview Round` = ‘First Round’ THEN 1 END)

WHEN `Interview Round` = ‘Third Round’ THEN SUM(CASE WHEN `Interview Round` = ‘Third Round’ THEN 1 END)/SUM(CASE WHEN `Interview Round` = ‘Second Round’ THEN 1 END)

…

END

What the second WHEN statement is trying to achieve is trying to achieve is a ratio that should represent a count of the number of 'Second Round' values divided by the number of 'First Round' values. In other words, what percentage of First Round interview candidates moved onto a second round interview.

Each ratio should be a count of the number of total interviews in that round divided by the count of the number of total interviews in the prior round.

DashboardDude

Hi @JasonKerr ,

Thanks for the clarification. Here's a video on the solution: https://loom.com/share/489fb795c735401681896cda6859402e

Issue was that you need a constant. SQL can only do math of a number if another number is on the same row so you have to use an ETL and the Group By function to make that constant.

Take a look and let me know if it works,

John Le

More video solutions at https://www.dashboarddudes.com/pantry

JasonKerr

Hi @DashboardDude,

First of all, that video was very impressive, I'll definitely be checking out your content in the future!

Unfortunately, I had already considered this as a solution, but the reason it doesn't work for my purposes is that the static constant in "Company A Count" that is created in the ETL is not affected by filters. So for my purposes, I'm looking to be able to track the "retention rate" just like your bar chart at the end of the video, but be able to filter by applicant specific information. For example, I'd like to be able to see this chart for "College Grads" and then jump to see the same retention chart for "Male" applicants and then look at "Male" applicants who are "High School Grads", etc. And ideally, I'd be able to just use the filters and one chart to be able to jump between those views quickly.

With the additional columns created in ETL, they won't change with with the filters and so the retention rates won't change with the filters either. This is why I was looking for a beast mode solution rather than an ETL/SQL fix.

DashboardDude

Hi @JasonKerr ,

Always trying to impress. So I think then you just have to put in those parameters like Gender, College level, etc in the Group By top part. If that doesn't work, feel free to schedule a free 30 min with me and we will knock it out together: https://www.dashboarddudes.com/book-a-dude

John Le

More video solutions at https://www.dashboarddudes.com/pantry