Count how many Providers submit a range of cases

dylang91
dylang91 Member
in Charting

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 

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • dylang91
    dylang91 Member

    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

Categories