To Count the records in each category

filippobe · January 12, 2011 11:34AM

Hi

I have a dataset with more than 100000 records. I have two field: Security ID and Portfolio ID.
Some securities appear more than one time in the database, as they are in more than one portfolios.
Therefore I forced the categorization of the Security ID field (more than 10000 different categories).
I grouped by Security ID.
Now I would like to add a field that tells me how many records I have for each Security ID (basically counts the records in each category) and after sorts the dataset for this field.
Is it possible? Should I use the recordcount formula? How?
Thank you

Filippo

tjbate · January 12, 2011 2:29PM

Filippo - forcing categorisation with that many different ID values is not a practical approach...the performance penalty is too big and there are other ways of counting and sorting. Try creating a formula field with this formula:

RECORDCOUNT(SUBSET(SecurityID))

This will give you count for each security and you can sort by it.
You can check this formula is correct by applying aggregation based on Security ID...the left side row header will give you the record count for each Security ID. In the Aggregation drop-down, set the aggregation function to be applied to your formula field to Mean (rather than SUM) and you should see the same value both the aggregated row headers and the formula field.

filippobe · January 12, 2011 3:47PM

It works!
Thank you very much tjbate.

Welcome!

Categories

Ideas Parade