Jump to content

Newbie Help WIth Average Function


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

Recommended Posts

First off I am a newb, so go easy.

We use Filemaker for our scheduling of orders in a screen printing business.

All orders are entered into schedule in a list format. One of the fields is GSP (Gross Sales Profit). Each order is given a GSP based on a formula we determined.

What I have been asked to do is get a daily GSP for each Dept on orders entered. I create a report each day based on a CTRL Find for that date. Then I sort by Departments. I would create 5 fields with this for each dept to have on a daily report.

I created a summary field that totals the number of orders per dept each day. But i cant seem to figure out how to make a new field with a calculation that is basically - If Dept = "A" then AVG GSP / Total Orders .

Any help would be greatly appreciated, and thank you in advance.

Link to comment
Share on other sites

Sorry, when you dont know the words its hard to articulate yourself properly.

I have attached a screen shot of the report I am creating.

As you can see its a line by line of our orders that shipped on 3/4. I have a formula that calculates the GSP (Our profit) for each order.

At the bottom I have the 5 Departments (Auto, Manual, Awards, Embroidery and AD Specialty)I have created a summary that counts up all the order per dept.

What I would like to do is have a field for each dept in the dept boxes below that takes all the Auto Orders , in this pictures case 11 of them , add all the GSP's and then divide by 11 to get an average GSP for Auto , and so on for each other dept. I get the found records by doing a manual CTRL F for that day so I dont need a date range of any kind in this.

gspjpg.jpg

Link to comment
Share on other sites

Are you married to this format, or can you change it? Filemaker's "native" way is to attach the sub-summary values to the group of records being summarized, e.g.

Department: Auto

• Order#: 123, Amount: 110

• Order#: 145, Amount: 200

• Order#: 167, Amount: 320

Orders: 3, Total: 630, Average: 210

---

Department: Manual

...

To do it the way your pic shows is more complex.

Link to comment
Share on other sites

Married, its part of several tables with same format, My sales group is avg age of 50 + who cant remember to hit CTRL N. Drastic changes are not an option.

Was hoping for a simple calculation with an If statement or something like that, im guessing its not going to be that simple.

Link to comment
Share on other sites

Well, you could split the values into a repeating calculation field with 5 repetitions, then use a summary field to summarize each repetition individually (I presume the structure of 5 departments is not likely to change often).

avg age of 50 + who cant remember to hit CTRL N.

Careful there, youngster. I could probably view this as ageism - if I could only find my reading glasses...

BTW, they should not be required to remember anything. It's your job as the developer to provide them with buttons and/or menus to make it easy for them.

Link to comment
Share on other sites

This topic is 4354 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.