grouping providers by how many times they submit

dylang91

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.

GrantSmith

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.

jaeW_at_Onyx

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.