Choose Language Hide Translation Bar
Highlighted
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
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
7 REPLIES 7
Highlighted
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
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
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
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
Highlighted
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
Super User

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

``roundedtime = round(:time / in hours(1))* in hours(1);``
Jim
Highlighted
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.