Choose Language Hide Translation Bar
Highlighted
sweetycha
Level III

How to group the low frequency data in "others"

Hi

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,

s

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: How to group the low frequency data in "others"

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

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

View solution in original post

7 REPLIES 7
Highlighted
txnelson
Super User

Re: How to group the low frequency data in "others"

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

If( Col Number( :Physician, :Physician ) < 20,
	"Other",
	:Physician
)
Jim
Highlighted
sweetycha
Level III

Re: How to group the low frequency data in "others"

Thank you Jim.

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

Highlighted
dale_lehman
Level VI

Re: How to group the low frequency data in "others"

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.
Highlighted
txnelson
Super User

Re: How to group the low frequency data in "others"

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

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

View solution in original post

Highlighted
sweetycha
Level III

Re: How to group the low frequency data in "others"

Thank you Jim. This works like a magic.

I have one more question.

How do we round the hour:min data into continious

like

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 

Highlighted
txnelson
Super User

Re: How to group the low frequency data in "others"

roundedtime = round(:time / in hours(1))* in hours(1);
Jim
Highlighted
sweetycha
Level III

Re: How to group the low frequency data in "others"

You are awesome 

Article Labels

    There are no labels assigned to this post.