Line graph with multiple lines of comparison

[JoeHardstaff]

Good afternoon,

I have come to a standstill in developing an analysis section of my database. 

What I want to do is very simple in most database or spreadsheet applications however, it just doesn't seem to want to work in Filemaker Pro Advanced 14.

All I want to do is chart the accumulative score for pupils over a period of 17 assessments. The assessment scores are -2 through to 3. So if assessment one is 2 and assessment two is 1 the accumulative total so far would be three. On the graph i want it to show Assessment one as 2 and assessment two as 3. I would like a line per pupil with the Y axis showing score and the x axis showing the assessment. The idea is that it shows progress over a year. 

I have all the data in a table but need to show it in a graph. 

Any suggestions would be greatly appreciated. I am sure you will need more info so if you don't mind could you ask what you need to know so that I can explain further.

Thanks in advance

 

Joe 

[comment]

You seem to have two separate issues in there:

The first question, regarding cumulative score, is easy: use a summary field defined as running total of score.

The second problem is much more difficult: Filemaker will not create data series (lines in the same chart) dynamically. Unless you know in advance exactly how many pupils will there be, this is not possible. However, it is possible - and quite easy - to show a chart for each pupil.

[JoeHardstaff]

Hi comment

Thank you for your reply. I can do the running total via a summary.

How do I get the chart for one pupil. 

 

Joe

[comment]

2 hours ago, JoeHardstaff said:

How do I get the chart for one pupil. 

Not sure where exactly you are having a problem with this. Place the chart in a subsummary by PupilID part, and set it to use data from the current found set, with the option to show data points for sorted groups. If you remove the body part, you will get a vertical stack of charts, one for each pupil.

[JoeHardstaff]

I have decided to use ExecuteSQL instead and make a dynamic line chart which is beginning to take shape. But thanks for the help anyway. 

[comment]

11 hours ago, JoeHardstaff said:

I have decided to use ExecuteSQL instead

I don't see what difference that will make. Filemaker chart tool will still not create additional lines dynamically.

[JoeHardstaff]

I used this page for the idea, seems to work well except the format of the date. 

http://onebusinessapp.com/diydashboard?aliId=236297707

[ggt667]

Perhaps this page can be of inspiration? https://github.com/mbostock/d3/wiki/Gallery

Edited April 14, 2016 by ggt667

[JoeHardstaff]

Hi Comment

I am still stuck on this topic. I have now created the single line graphs per pupil, I even managed to implement drop down menus with subject and pupil name that filters the chart showing selection. Still only one line at a time but works well. I scrapped the idea of using SQL as it still didn't do what I wanted. 

Now I have another requirement. I am working on a line graph again but this time its for effort score per pupil per class. Again i can get individual pupil lines. I now want to compare groups of pupils. I can get single lines using a summary field for average effort score for boys or girls etc over a period of time, but i'd really like to be able to show the average overall effort score compared lets say boys. i have tried to create another instance of the effort score table filter that and use that as a series which almost works but the dates (x axis) go a bit strange and the actual scores seem to be wrong as i have to specify a different table to that in which the layout is based. 

I did have another idea of creating a fresh table that has a calculated field for all the comparison groups which populates with the summary field using  a script that sets the field based on a filter. Then use the newly created fields to make multiple lines on the line graph... Seems like a long winded way but I think it would work if i could accurately get the data in the fields.

 

What do you think?

I really appreciate the help by the way. Learning a lot.

Thanks

 

Joe 

 

[comment]

I am afraid I got lost in your description.

Here's a rather simple rule-of-thumb concerning comparison charts, i.e. charts showing more than one data series: the number of data series must be known in advance.

If you want to compare boys to girls (2) or this year to the two preceding years (3)  or the quarters of a year (4) - that's all possible. It's not always trivial to gather the data required to populate the chart, but it is possible.

But if you want to compare groups of pupils, and the number of the groups can vary from one found set to another, it just cannot be done (though you can still allow comparison by showing an individual chart for each group, one below the other).

[JoeHardstaff]

Hi Comment

Me again. I have progressed...

I have another charting problem...

We have a record for each pupil (we are a small independent school) for each week to record their effort in each class (6 per day). So each record stores Monday lesson 1, Monday lesson 2 etc, Tuesday lesson 1 etc etc. It has a total and average also. This bit is fine, all works. 

I have made a layout that sorts all records by week commencing and then by score (descending order) with sub summaries that includes the average score for all pupils for that week. Again works really well. 

The chart I want to produce is a line chart that shows effort score averages over the weeks. I can get this for individual pupils, I can even get this for pupils in a certain category such as male/female but what I really want is to be able to have multiple groups on one line chart so that we can compare.  

My thinking on this is that I create the instances of the effort score table for each group (male, female, free school meals etc) each one filtered so that the average in the summary field only applies to that group. Then create a line graph that uses a new series based on each instance of average score in the table. BUT, I think I need a table that I can use as the layout source that doesn't use one of the existing instances or else the data is not accurate. So I kind of need a middle table to base the layout on that can then be related to  each instance of effort scores to display the data accurately. 

Gosh I do hope this makes sense.  

I would really appreciate your help on this as its been a big issue for me for some time. Once I crack this I can then use the same principle for other data we have. 

I know this is a similar issue to that previously mentioned but this may be a bit more simple to understand???

 

Thanks 

 

Joe