Dynamically Chart a Date Range by Year

[AnthonyDixon]

I am currently charting a date range within a given year with no problem, but I'm am trying to chart multiple years if

the date range spans multiple years.

 

Can't seem to get this one. Thanks for any help.

 

Please see my demo file.

 

Take Care

Anthony

 

FMP12 Advanced

Mac/PC

Dynamically Chart By Year and Month.zip

[eos]

AFAICT, the problem is that you're using a fixed number of month records to collect the related data and act as data points. Of course, this breaks or shows wrong results when the date range spans more than 12 months.

 

I assume the solution to your problem would be to “Chart delimited data”, as outlined in this section of the FileMaker 12 help:

 

http://fmhelp.filemaker.com/fmphelp_12/en/html/create_chart.12.15.html#1078900

 

I don't really work with charts, so I'm not sure if you must use a field or if a $$variable would do. Anyway …

 

To create the lists, ExecuteSQL would be my first choice; use a script to loop through the date range by incrementing the month part; populate a $listOfMonthAndYear with the year and name of the current month, then query the number of matches, and use that result to populate your $listOfResults. At the end, use the $list… vars to set two pre-defined fields that you use for the chart.

 

Note that you need to set the $variables in each iteration of the loop, and check for the correct Exit Loop condition. 

 

That's a method similar to one I used to create a cross-tab report in a Web Viewer (which might be an alternative to using FM's built-in Chart tool, if you feel comfortable with a bit of HTML and CSS). 

 

Other techniques would probably involve a looping script (again ), some summary fields, sorting and GetSummary().

 

Don't hesitate to ask if you need more implementation details.

[AnthonyDixon]

Thanks for your reply, the reason I’m using the months utility table and not delimited

data is to display all the months in the year even if there are no record for that particular

month.

 

Maybe I’m approaching this wrong, just not sure how to display multiple years for the

months being displayed in the chart if a multiple years are selected in the date range.

 

Thanks Again

Anthony

[eos]

This might help you along.

 

Note that in this setup, no intermediary Months table is required. Also a number format is used for finding the dates; someone who is more SQL savvy will probably find a cleaner way to do.

 

A tip: if you wished (for whatever nefarious reasons …  ) to suppress certain results (above/below a threshold etc.), you could wrap the setting of the two list variables into an If ( result your condition … ); then there would no data points and no labels displayed for those months.

Dynamically Chart By Year and Month_eosSQL.fmp12.zip

[AnthonyDixon]

Thanks for the start and different approach. I don't do much with SQL and I try to stay away from looping scrips

but I will give it a try. It seems it may work for what I'm trying to accomplish.

 

Thanks for all your help and suggestions