9 Replies Latest reply on Mar 11, 2015 2:56 PM by philmodjunk

    Understanding Line Charts

    SydneyBlock

      Title

      Understanding Line Charts

      Post

      Hello - 

      I am trying to create a chart that looks like the image below. I want to show the average yearly salary increase per department by year. This is currently in a database of personnel records. Compensation(including compensation year & increase amount) and position(including department) are both on separate tables, and both are related to the personnel records table, but not to each other. 

      I have very little understanding about quick charts in general, but even less about how line charts are made. Can anyone explain to me? What would need to be done in order to accomplish my goal?

      ChartExample.png

        • 1. Re: Understanding Line Charts
          SydneyBlock

          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.

          • 2. Re: Understanding Line Charts
            philmodjunk

            A more detailed description of your data model would help. Is this it?

            Compensation >--employees--<position

            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. 

            • 3. Re: Understanding Line Charts
              philmodjunk

              That would be one RECORD for every year of employment. 

              • 4. Re: Understanding Line Charts
                SydneyBlock

                Nothing really happens when an employee's position changes. This is the portal itself.

                • 5. Re: Understanding Line Charts
                  philmodjunk

                  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.

                  • 6. Re: Understanding Line Charts
                    SydneyBlock

                    I apologize.

                    I do not really have a way to show persons moving departments. The relationship links are like this;

                    • 7. Re: Understanding Line Charts
                      philmodjunk

                      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.

                      • 8. Re: Understanding Line Charts
                        SydneyBlock

                        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.

                        • 9. Re: Understanding Line Charts
                          philmodjunk

                          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.