I'm new to FM and I'm using FM 13. I'm trying to create a line chart. First column has account names next 13 columns have sequential dates that contain sales numbers. Thanks!
Hi. Welcome to FileMaker.
Here's a great guide to get you going in charting: In-Depth: Charting . I'd suggest a read through this.
A chart can pull data from one of these three sources:
1. a field in the current found set. So if you have 12 records, each with a field called Sales, you can chart those on the graph.
2. A set of related records. If you have a SalesPerson and she has 12 sales, you can graph her 12 sales.
3. A return delimited list (Current Record): A list of data such as "1¶2¶3¶4" and so on.
It seems you want to want to use the latter of these two in the Data Source tab of the chart set up. But your data structure might be difficult to get on the chart. Can you post a screenshot of a record of data?
Thanks for the quick reply.
Here is a screen shot. Fyi the date format is different in first couple columns because I was playing with the date format. In Excel I just highlight the headers and the account to chart and it spits it out. I know I missing something here on how it works in FM. Thanks again.
Are you looking to chart by date? So show one account's name and all that person's dates/values?
What would be the end result in Excel?
Each account would a be a data series.
The x-axis would require the dates, but at the moment your dates are the fields.
Each point on the line chart is the value at that account's dates.
Or, do you want to chart one person's data?
FileMaker charting requires you put in a field or a return-delimited list for the x-axis and the y axis. So ideally, for account Jeremy I'd have the x-axis be:
and the y-axis would have the values that correspond to each date:
That would chart my information well.
Snce you have the dates as field names, (I have a suggestion for a different approach later) you'd have to hardcode the list of dates into the charting tool. That's fine but it also does not easily scale.
You could use the List function to return a list of all the fields for the Y axis: List ( YourTable::10/30/2017; YourTable:: 11/6/2017 ; Yourtable::11/13/2017) and so forth. BUt you'd still have to hardcode the values for the x-axis.
How far into this project are you? Typically data like this should take advantage of the relational nature of databases. You have one table called "Accounts" and then one table called "Sales" (or whatever value those are). These two tables would be related to each other via a key. You could easily then chart the data (no hardcoding and very easily scaled ) for one account at a time or possibly the entire account.
Yes. Here is what it looks like in Excel.
Attached is a file that shows how the data should be constructrued in order to be easily scaled. It is different than Excel, for sure, but it is the relational way to handle these things. Anytime more than one field holds the same 'kind' of data (such as values from dates or phone numbers) it is a good idea to consider this parent / child relationship set up.
And it makes it easy to add data. If you go to Record #2 and start to enter more records in the list (the portal) you'll see those quickly populate the chart.
Is it feasible to work your data into this structure? That would be a different question, but it can be done.
JB thanks for the help. Btw the file was not attached. thanks
If you're looking at this through email, you probably wont see it. But it is attached to my previous response.
Ok thanks , I was
You've been a great help!
Sent from my iPad
Our data is download like that. Is there a way to make it work? thanks
Retrieving data ...