Frequency of Occurrence

[FMPOK]

Greetings.

I am trying to do what I believe is a simple task, which I have not been able to accomplish to date. I have a database the contains several fields with drop down lists. Each quarter I need to be able to deternmine the number of records in that date range that contain each of the list options (e.g. "outpatient", "inpatient", etc.). A quarters worth of data generally conatins about 3000 records. I want to be able to determine the frequency with each each of the possible choices in each of the fields with drop down lists occurs.

Does anyone have any suggestion (PLEASE!)?

Thank you in advance!

[SlimJim]

I assume that you wish to produce a report with the frequencies. I have not done this myself so these suggestions are somewhat theoretical. I am assuming that each field will contain only one of the available options.

The direct method (equivalent to ticking on bits of paper!) would be to add a count calculation field for each value in a list, e.g, IsOutpatient = (optionfield="outpatient"), etc and then add in summary fields of Count(IsOutpatient), Count(Is...) etc.

If there are lots of variables that you want to count then this is going to add a lot of extra fields and if you ever change the values on the option list is going to require a redefinition of the fields.

Another option would be to script the counting. For this it might be best to construct a global repeating field with each repetition a value on the list and another "matching" global repeating field for the counting, this at least makes it a bit easier to change the options. The counting would have to be done by a double loop. Outside looping through the fields in the quarter and the innerloop running the value count for each value. You can cut down the inner loop by jumping out as soon as you hit the value, but if you are only doing this once a quarter it doesn't really matter. You will end up with the frequencies as the values of a repeating field.

These methods are somewhat "brute force" and I will be interested to see alternative more subtle suggestions.

[comment]

I am afraid anything LESS subtle would have to resort to finger counting...

Why not create a report with a subsummary part (when sorted by Type). Then put a summary field (Count of PatientID) in the subsummary. Another summary field (Fraction of PatientID) can provide the percentage of each Type in the total found set.

[SlimJim]

It was after all an implementation of finger counting...

Your method is significantly easier so today I have learned something new. I have constructed a simple implementation of this to save for my own use but I cannot find the Fraction of function, only Fraction of Total and that produces strange results so I haven't understood this bit properly.

[comment]

Yes, I meant Fraction of Total. See the attached for how it might be used.

summaryByType.fp7.zip

[SlimJim]

Thanks. This is what I tried to do but with a more simple-minded set of data. If you look at the preview report you will see what I meant by strange results. For example there are 44 records each of type "Four", "Two" and "Three" but the percentages assigned are all different "17.9", "15,9" and "13.3". From the point of view of the original post your example answers it perfectly (without the Fraction of Total summary)

I have not seen this function used before but I think the problem is that the records are not equally weighted, we could do with Fraction of Count. I have experimented by adding in a constant field One of value 1 and using Fraction of Total (One) this produces the correct percentages.

[comment]

Yes, of course, how stupid of me not to notice I have attached it to the wrong field. When counting fraction of total of RecordID, it goes up as you progress in the list... A constant =1 is indeed the required field. Thanks for catching that.

I have replaced the attachment with a corrected one.