Maybe it would help if I specified the data relating to salary increases is in a portal. I want to average the increase amounts for the portal data and have this information grouped by department so I can see the average increase amount per department for the year.
A more detailed description of your data model would help. Is this it?
With one year in compensation for every year of employment? And what happens when an employee's position changes?
All told, this chart will probably need to use delimited data and executeSQL functions.
That would be one RECORD for every year of employment.
Nothing really happens when an employee's position changes. This is the portal itself.
Sorry but a screen shot of a portal does not document the relationships between the three tables that you describe and you have not confirmed nor corrected my guess as to what you have.
You've indicated that you have a related table the record's their position and department. How is that table linked to the employee table? How do you update that data if the Employee's job changes? In particular, if an employee moves from Department A to Department B, what do you do with your data to show that last year this was an employee in Department A and this year they are in Department B. This, even if an unlikely event, is a key detail affecting the data available for your chart.
I do not really have a way to show persons moving departments. The relationship links are like this;
If you do not have a way to show that change, then your chart will only be accurate so long as no such change takes place as the chart will assume that while an employee's compensation may change each year, the department that employs them does not. That may be a detail sufficiently unlikely has to have no significant effect on your charted averages for each department.
Do you know how to construct an SQL query using the executeSQL function? That seems the best approach here, but the resulting calculations are pretty cryptic--especially if you are not familiar with SQL. (This also requires using FileMaker 12 or newer.)
Let me know and if SQL isn't something you are prepared to tackle, we'll take a shot at a combination of calculation fields and relationships to get the same result.
I am trying to do some research on learning how to work with SQL, but am really not that familiar. Calculation fields are more my speed at this point.
I will assume that Position::Department names the department for each employee.
I've rethought this a bit and we'll need to create a layout based on Yearly compensation for the purpose of generating this chart.
Define a series of calculation fields, one for each departement in the Yearly Compensation table:
If ( Position::Department = "Production" ; Raise Amount )
For each of these calculation fields, define an "average of" summary field that averages one of these calculation fields.
In chart set up define a Y data series for each department and specify the appropriate summary field for each.
For the X - series, specify Year ( YearlyCompensation::Year ) to get the values needed to label the X - Axis.
Now make sure that the chart's data source options specify "found set" and "summarized data".
Sort your records by YearlyCompensation::year.