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

user10986
user10986
in Charting

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!

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @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`)))



    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user10986
    user10986
    edited November 2022

    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
    DavidChurchman Member

    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.

Categories