Need help with Charting

[blissland]

I've been using FileMaker for over 20 years, but up until today had never tried making a chart with it.  

I'm tracking the dates that students inquire about my school and would like to make a line chart that shows total per month for each year.  I have the date field, a month(date) field, and a year field.  There are a few hundred records.  It's clear that I need to do something else to get this to work but I'm not sure what it is.  Do I need to create 12 global fields that count the number of records for each month?  If so, is that what I would put on the y axis?  Do I need to add something to the relationship graph?      

[comment]

3 hours ago, blissland said:

make a line chart that shows total per month for each year

By "total" I assume you actually mean "count".  But what exactly does "per month for each year" mean? Do you want to show a single line showing a data point for each month, year after year - or do you want to compare the years, so that there are only 12 data points and a separate line for each year?

If it's the latter, you will face a difficulty using the FM built-in chart: it requires you to pre-define the series you want to show. IOW, you can only compare say the last 3 years. If you want to show a series for each year, without placing a limit, then you will need to use a different charting tool.

 

 

[blissland]

yes, by total i mean count.    I would like to have months on the x axis, counts on the y axis, and different lines for each year.  I might settle for summarizing across all years.  Sounds like the year by year idea is not doable with FM, so that's very helpful to know that I should stop banging my head on this.  

[comment]

2 minutes ago, blissland said:

Sounds like the year by year idea is not doable with FM

As I said, it is doable only if the number of compared years is fixed in advance. So for example, you could select the year 2023 and compare it to the two preceding years (2022 and 2021). Here, the chart object would have 3 pre-defined series.

P.S. Please update your profile so that we know which features you can use. And for this specific question, your skill level is also relevant.

 

[blissland]

profile updated.  I have developed this solution myself.  I use scripts.  I make and modify relationships.  

I would have 9 pre-defined series at this point.  And each year I would add 1 more.  

[comment]

1 hour ago, blissland said:

I would have 9 pre-defined series at this point.  And each year I would add 1 more.  

I don't know if that's a good idea. A chart can have at most 12 data series - so if you already have 9 years of data, you will max out the chart in just 3 years.

FWIW, I am attaching a demo of how this could be approached. This does totals, not counts, but the adjustment is trivial.  For each year you want to add, you must add 12 records to the MonthSlots table and a data series to the chart.

A more advanced method would collect the chart data into a variable, using the FastSummaries method. Then you wouldn't need the MonthSlots table.

 

 

CompareYearsByMonthSlots.fmp12

[blissland]

Thanks for this.  looking at it.  haven't quite figured out how it works.  Can you tell me why you reduce the dates to the first of every month and count those rather than just extracting the number of the month and counting those?  

[comment]

38 minutes ago, blissland said:

why you reduce the dates to the first of every month

It's a convenient way to create a value that's common to all records in the same month of the same year. You can then use this value to group records by month (of the same year) or - as in this case - to link them to a common parent in the MonthSlots table that has a record for each individual month (in every year) with the same date.

 

[blissland]

ok.  but if I want to have a line graph with a line for each year and have months on the x axis, wouldn't I need a year field and a month number field rather than have them combined in the same field?  

[comment]

You need a separate stack of 12 values for each series. The way my demo achieves this is by looking at a list of all 36 values - produced by List ( MonthSlots::cTotal ) - and then having each series grab its portion of 12 values using the MiddleValues function.

 

[comment]

Your question gave me an idea for another approach that does not require an auxiliary table of slots. You only need to add a data series for each year above the starting year.

However, this method works only for counts - adapting it for totals would be quite a challenge.

 

CompareYearsByMonth_Count.fmp12

Edited November 12, 2023 by comment

[blissland]

I will take a look at this.  It may take me a few weeks or months before I can come back to this issue.  Thank you.