People like using cats in this forum and it keeps it simple. Let's say I have 3 types of cat (C) - Moggy (M), White (W) and Siamese (S). I am recording prices and using these prices for a report which produces their average price. I want to work out the premium that one breed of cat has over another expressed as a percentage of, say Moggies.

I have 3 prices for Moggies: $20,$21,$22; 3 prices for White cats $30,$31,$32 and 3 prices for Siamese - $50, $55, $60.

My nice summary report produced from a script shows the average prices OK but how can I work out the premium for the other breeds given the calculation has to be on a value which is already a summary?

Cat M Av price $21

Cat W Av price $32 Premium $XX? (calc is (32-21)/21*100)

Cat S Av price $55 Premium $XX?

 

The average is worked out as a weighted average (just in case that is part of the solution)

This is not simple, because while you can group records by category and calculate the average of each group, there is no built-in mechanism by which one group can access the sub-summary values of other groups.

There are several ways around this obstacle - I will mention two:

1. Work this out across a relationship; create a table of Types (3 records in your example), use a calculation field to get the average of related records, a summary field to get the minimum of the averages, and another calculation field to calculate the premium from that. Note that this method will consider all related records, not just the found set.

2. Run a script prior to producing your report to determine the lowest group average and store it in a global field or variable, to be used later by the calculation field that calculates the premium.

 

31 minutes ago, enquirerfm said:

The average is worked out as a weighted average (just in case that is part of the solution)

That's kind of meaningless when you don't tell us what is it weighted by.

Thanks - this was a helpful start. I have gone for (1) and I am obtaining the results I need.

I wanted to obtain an average which was weighted by the quantity - this is proving more difficult to do. However, just needed to get my mind around all of my fields and managed it... Thanks again.

Edited January 15, 201610 yr by enquirerfm
Finalisation