Sorting data by success percentage

Jbrorby

I have a card where we are looking at blood draw procedures year to date, graphed by month.   So, we have the date of the blood draw as a column, we have the count of distinct needle sticks, and then one column that categorizes each blood draw as successful or unsuccessful.   

 

We are not so much worried about the actual counts, rather the success percentage rate per week.

 

The 100% stacked bar chart was useful in the sense that it turned the Y axis into percentages instead of count of blood draws.   It will assign the percentage of successful and the percentage of unsuccessful.   But the problem is that every week is going to add up to 100%, and so I don't know how to sort the bars by percent of successful.   In other words, if week 1 had 10 blood draws and a 90% success rate, and week 2 had 20 blood draws but a 50% success rate, I want it to sort by the higher success rate, not the higher blood draw count.   

 

My next thought was to do a beastmode.   If the "success/unsuccess" colmumn is "success" then '1' else '0'.   Then I would sum that column and divide it by the total count of draws.  Then my success column would be a percentage on its own, versus converting counts into percentages in the graph.   The problem I have here is that the count of draws is a distinct count because there are duplicates.    So when I sum the new 1 or 0 column, it sums the duplicates.   So in a distinct count of the draws, there may be a distinct count of 4 draws that were successful.   But in my '1' or '0' column, if there is a dupplicate successful draw, they are each given a 1 and they are each summed in my sum of successful draw beastmode.    So I could have 4 draws with a sum of 5 successful, which isnt correct.

 

I think I could solve this with a rank and window in ETL, but we're trying to find a way to resolve this without creating another flow.

ST_-Superman-_

What defines a "unique blood draw"?  Presumably there is some kind of draw_id field?

 

In that case, you would want to do something like this in your beastmode:

COUNT(DISTINCT 
CASE WHEN `Successful/Unsuccessful` = 'Successful' then `draw_id` END
)
/
COUNT(DISTINCT
`draw_id`
)

Jbrorby

Hey, I remember you from Domo in Chicago.   And thanks, I will try that.

Jbrorby

For the portion count (distinct case when 'successful/unsuccessful' = 'successful' then 'draw id' end), it was giving the NULLs a count as well.   So I modified your idea a bit to:

 

case when (case when 'successful/unsuccessful' = 'successful' then 'draw id' end) is not Null then Count(Distinct case when 'successful/unsuccessful' = 'successful' then 'draw id' end) else '0' end

 

This seems to solve my problem of duplicate 1 counts.

 

But when I try to do  the above formula/distinct count draw id, and I put it in the Y axis, I am getting a '1' when I was hoping/expecting to get '0.95' (which is the % if I do the math manually).   

 

So, I thought maybe I need to sum both formulas before I divide them.   Sum of the above formula divided by Sum of distinct count draw id.   But Domo does not seem to like when I try to sum these distinct counts.   And when I put this formula in the Y axis, I get the "an error has occured, unable to compute request" message.

 

But I would say your suggestion resolved my question from the stand point of eliminating the duplicate 'success' counts

ST_-Superman-_

When you drop the beastmode in the y-axis (the one that gave you 1 as a result) can you confirm that you have the format set correctly?

 

The default is for it to be a number with no decimals so .95 would be rounded up to 1

 

Another question I would have is can `draw id` be null?

 

Also, if `successful/unsuccessful` is null, do you still want to count the draw id in your denominator?

 

If you want the nulls filtered out altogether, you could use a separate beastmode as a filter:

ifnull(`successful/unsuccesful`,'exclude')

You would then put this beastmode in the filter section and exclude the 'exclude' values