Count how many Providers submit a range of cases

dylang91

Hello, I am trying to segment our Providers by how many cases they submit.

I have the formula below but I have something wrong. Ideally it will tell me how many Providers submit between 1 and 10 cases and 11 to 20 cases labeled them as Provider 1-10. This count will change throughout the year as they continue to submit cases. When I know a case has been approved is then `FirstSetupApprovedDate` has a date, if it is blank I wouldnt want to count it

I hope someone can help

CASE

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID`) >= -1 AND <= 10 then 'Povider 1-10'

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID`) >= -11 AND <= 20 then 'Povider 11-20'

end 

MarkSnodgrass

Your AND statement is slightly off. You are also missing an END statement inside the count. You have to re-state what you are evaluating, like this:

CASE

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -1 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 10 then 'Povider 1-10'

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -11 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 20 then 'Povider 11-20'

end 

You might also consider moving the case statement to the very outside, which might work better:

CASE when `FirstSetupApprovedDate` IS NOT NULL then
(CASE WHEN COUNT(`CaseID`) >= -1 AND COUNT(`CaseID`) <= 10 then 'Povider 1-10'
WHEN COUNT(`CaseID`) >= -11 AND COUNT(`CaseID`) <= 20 then 'Povider 11-20'
end)
END

Hope this helps.

MarkSnodgrass

Your AND statement is slightly off. You are also missing an END statement inside the count. You have to re-state what you are evaluating, like this:

CASE

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -1 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 10 then 'Povider 1-10'

WHEN COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) >= -11 AND COUNT(case when `FirstSetupApprovedDate` IS NOT NULL then `CaseID` END) <= 20 then 'Povider 11-20'

end 

You might also consider moving the case statement to the very outside, which might work better:

CASE when `FirstSetupApprovedDate` IS NOT NULL then
(CASE WHEN COUNT(`CaseID`) >= -1 AND COUNT(`CaseID`) <= 10 then 'Povider 1-10'
WHEN COUNT(`CaseID`) >= -11 AND COUNT(`CaseID`) <= 20 then 'Povider 11-20'
end)
END

Hope this helps.

dylang91

Thank you it worked. I am trying to get a vertical graph to split the by provider "level" I think the formula works but I am still working on my graph