How to get % of total in Nested Bar and Line Chart

user10986

I'm trying to utilize the nested bar and line chart to determine to show how many of our signed staff actually start vs. drop out in onboarding. Currently, I have the chart as a nested bar and it works well but when I switch it to nested bar and line, I'm not able to achieve my goals. As some background, the current chart is:

• x axis is signed offer date over time
• y axis is count (employeeID)
• series is onboarding status (and the onboarding status fields are started, future start and did not start) which provides me 3 bars showing how many are in each.

The goal is to leave that chart intact but add a line overtop that is % dropout rate with the calculation being onboarding status=did not start divided by the total of the nested bar (i.e. started + did not did + future start).

If anyone has any ideas here, that would be greatly appreciated.

Thanks!

ST_Superman

@user10986

You could try something like this:

sum(case when `onboardingStatus` = 'did not start' then 1 else 0 end) / (sum(sum(case when `onboardingStatus` in ('started','did not start','future start') then 1 else 0 end)) over (partition by year(`signedOfferDate`), month(`signedOfferDate`)))

user10986

Hi - thanks for the reply. It did not work though. I've attached the formula and what the chart looks like using that formula. I also attached the baseline chart - that has everything except the dropout rate (line).

Any help would be greatly appreciated.

Ideal Chart.JPG

Chart Settings.JPG

Formula.JPG

DavidChurchman

I think there's more than one thing going on, but I would start by playing with your sorts. I don't think you want a sort on Onboarding Conversion status, which is what's causing the duplicate dates across your x-axis.