nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to group the low frequency data in "others"

sweetycha

I have a physicians name data of each patients (row). I want to group all physicians name into "Others" who have seen less than 20 patients per month.

I have 3 months Jan 1 to March 31 data

Thank you

Best,

Accepted answers

txnelson

This should get you what you want, the count per month for each physician

If( Col Number( :Physician, :Physician, month(:datecolumn )) < 20,
	"Other",
	:Physician
)

All comments

txnelson

Create a new formula column of Data Type Character, and give it this formula

If( Col Number( :Physician, :Physician ) < 20,
	"Other",
	:Physician
)

sweetycha

Thank you Jim.

Can we also add another condition of if less than 20 per month not just entire study period?

dale_lehman

The Col Number function permits a second argument for a By group, so you could do it by month. I'm sure Jim can give you the script for that, but I just use the mouse and the calculator window.

txnelson

This should get you what you want, the count per month for each physician

If( Col Number( :Physician, :Physician, month(:datecolumn )) < 20,
	"Other",
	:Physician
)

sweetycha

Thank you Jim. This works like a magic.

I have one more question.

How do we round the hour:min data into continious

4:56 is 4 hours and 56 mins I want this to be 5 hours

3:29 I want this to be 3 because I want to pass the 30 mins to round up