Jump to content

Calculations on summary fields in a report


This topic is 2356 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by enquirerfm
Finalisation
Link to comment
Share on other sites

This topic is 2356 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.