grouping providers by how many times they submit

dylang91
dylang91 Member
edited March 2022 in Charting

Hello, another user was able to help me with the formula but I am still working on the graph.

formula test123 it counts how many times a provider submits a case, then it segments it into how many cases they submit per year. The system recognizes as a submitted case when there is a date in FirstSetupApprovedDate.

Note that a provider can be in the data several times so I dont know if I should add Distinct function


CASE when `FirstSetupApprovedDate` IS NOT NULL then

(CASE 

WHEN COUNT(`Provider`) >= -1 AND COUNT(`Provider`) <= 10 then 'Povider 1-10'

WHEN COUNT(`Provider`) >= 11 AND COUNT(`Provider`) <= 20 then 'Povider 11-20'

WHEN COUNT(`Provider`) >= 21 AND COUNT(`Provider`) <= 50 then 'Povider 21-50'

WHEN COUNT(`Provider`) >= 51 AND COUNT(`Provider`) <= 80 then 'Povider 51-80'

WHEN COUNT(`Provider`) >= 81 AND COUNT(`Provider`) <= 150 then 'Povider 81-150'

WHEN COUNT(`Provider`) >= 151 AND COUNT(`Provider`) <= 400 then 'Povider 151-400'

WHEN COUNT(`Provider`) >= 401 AND COUNT(`Provider`) <= 50000 then 'Povider 400+'

end)

END


I am using a BAR . FirstSetupApprovedDate as X axis and then Y axis the formula above and ideally i would like to see a bar for each segment.


Answers

  • GrantSmith
    GrantSmith Coach
    edited April 2022

    Hi @dylang91

    First thing I would simplify your beast mode. If you go in descending order of your buckets so that the largest bucket is first you can remove the secondary conditions on each of your cases because the CASE statement will evaluate the first and only first condition where it's true so future conditions are implied they don't meet the prior conditions. Secondly you're attempting to compare a single row value (FirstSetupApprovedDate) but also an aggregated value (COUNT). You'd need to move your condition into your aggregation and it can be simplified since you're wanting to count the number of records which aren't null

    CASE WHEN COUNT(`FirstSetupApprovedDate`) >= 401 then 'Provider 401+'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 151 then 'Provider 151-400'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 81 then 'Provider 81-150'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 51 then 'Provider 51-80'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 21 then 'Provider 21-50'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 11 then 'Provider 11-20'
    WHEN COUNT(`FirstSetupApprovedDate`) >= 1 then 'Provider 1-10'
    END
    

    Also as a general tip whenever I'm diagnosing beast modes I'll typically break them down into smaller component and use a table chart to see and understand what exactly each piece is doing more easily.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • you can't create graphs based on bucketed activity that's been aggregated in analyzer. you'd have to materialize the buckets in ETL or a dataset view and then create a graph on that column.

    Do an ETL where you COUNT and GROUP BY your Axis then JOIN that dataset back to the original transactions

    unfortunately that means your buckets would not respond to filters in analyzer.


    The reason why you can't CASE WHEN ... COUNT() is b/c the label ("provider 1-10") happens AFTER aggregating your data (i.e. this is your first (and only allowed) GROUP BY clause. So when you ask to SUM(metric) GROUP BY BUCKET_COLUMN, under the covers you're asking Domo to issue a second group by statement. which Analyzer does not support.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"