Success Rate %

louiswatson · 2023-03-02T15:57:52+00:00

There was an error rendering this rich post.

To work out the success rate I need to (a) count number of rows where 'Status' column has 'Application Granted' and divide this by (b) count number of rows where there is a value in 'Determination Date' column.

A / B = Success rate

Any help would be much appreciated, cheers

Find more posts tagged with

Magic ETL

Beast Mode

Accepted answers

MichelleH

@louiswatson It sounds like you're almost there already. How about this:

(count(case when `Status` in ('Application Consented','Application Refused','Waiting for Decision') then `Status` end)
  + count(`Appeal Determination Date`))
/ count(case when `Status` in ('Application Consented','Appeal Allowed'))

All comments

MichelleH

Hi @louiswatson try this:

count(case when `Status` = 'Application Granted' then `Status` end)/count(`Determination Date`)

louiswatson

Thankyou Michelle, this worked!

As a slightly more advanced calculation, how would I do the below calc?

Count how many applications have a status of 'Application Consented', 'Application Refused', 'waiting for decision'
Count number of rows where there is an appeal determination date
Add together 1 and 2
Count number of applications with a status of 'Application Consented' or 'appeal allowed'
Divide step 4 by step 3

Many thanks for your help.

MichelleH

@louiswatson It sounds like you're almost there already. How about this:

(count(case when `Status` in ('Application Consented','Application Refused','Waiting for Decision') then `Status` end)
  + count(`Appeal Determination Date`))
/ count(case when `Status` in ('Application Consented','Appeal Allowed'))

louiswatson

I seem to be getting a syntax error, there was a bracket that looked like it didn't need to be there. Any ideas?

count(case when `Status` in ('Application Consented','Application Refused','Waiting for Decision') then `Status` end)

+ count(`Appeal Determination Date`)

/ count(case when `Status` in ('Application Consented','Appeal Allowed'))

MichelleH

@louiswatson Apologies, I forgot to complete the last case statement:

count(case when `Status` in ('Application Consented','Appeal Allowed') then `Status` end)

louiswatson

Thankyou Michelle, really helpful!