Formula field: % of adjusted total

filippobe · February 22, 2011 6:42AM

Hi,

I need your help with a formula.
I've a dataset of securities with two fields: Security ID and Pricing status (currently priced, matured, unpriced...).
I aggregated by the Pricing status and I created a formula field (N. of securites) to count how many Security ID there are for each pricing status category.
I created an other formula field to express the number of security for each pricing status category as % of the total: =(N. of securities/ALLRECORDCOUNT).
Now I selected only some pricing status categories with a query and I need to count all the record in this subset.
Which is the formula to count all the record in a subset (adjusted total or subtotal)?
After that, how can I express the number of securities for each pricing status category as % of this adjusted total?
Could anyone please help me?
Thanks

Filippo

steve · February 23, 2011 7:24AM

Please could you post a sample IOK here and we'll take a look.

filippobe · February 23, 2011 8:32AM

Please find attached sample IOK.

As I selected only 4 pricing status categories, the number of records ("adjusted total") is 35.
What I would like to do is to add a new formula field, that shows the number of securities for these 4 categories (Currently priced, Removed, Should be able to evaluate, Unpriced) as % of the adjusted total.
I hope now is clear

Thank you

steve · February 24, 2011 7:04AM

Ok, so in this file, for Unpriced, you want to see 13/35 = 37%, etc.?

See attached file. I have added an unaggregated table view to show you what's going on.

I've changed "N. securities" to have the formula "1". This simply means "put 1 in every record". I've changed the aggregation settings to use Sum, instead of Formula Result. This is just an alternative, easier way of getting the record count.

You can't use ALLRECORDCOUNT - this yields the number of records in all data (52). You can't use RECORDCOUNT - this yields the number of records in the aggregated view (4). There is no function yet to get the unaggregated record count.

So I've changed "% of total" to have the formula:
[N. of securities] / SUBSET_SUM([N. of securities])
and made sure the aggregation setting for this field uses Formula Result. This has the desired effect.

filippobe · February 25, 2011 9:20AM

Thanks Steve.

At this point I am wondering which is the best solution to count the records with the aggregation.
N. of records = RECORDCOUNT SUBSET (with mean) or simply "1" (with sum)?

I mean in term of performance and suppleness as basis for other formula fields/calculation.

steve · February 25, 2011 9:46AM

The quickest in terms of performance would be to use "Record count" in the aggregation submenu. But there's very little in it.

Welcome!

Categories

Ideas Parade

Tagged