9 Replies Latest reply on Feb 12, 2014 10:44 AM by philmodjunk

    Chart modifying



      Chart modifying


           Hi all, 

           I'm currently working with information that is tracking sales by month and by item. They are labeled by the month in text form (i.e. November) rather than 11 or something of that nature. Is there a way to sort them by month without having to change them to a numerical date? 


           Also, when I try and make a chart it doesn't allow me to total the amount purchased, rather the amount of times something was purchased by month. Is there a way to fix this? i keep trying sum (total due) but it is giving me the count of total due instead. 


        • 1. Re: Chart modifying

               How is your data set up in the table from which you are charting this data?

               Do you have multiple records for each month and a number field that shows an amount (don't know if that's a quantity or a dollar amount).

               You can define a summary field in this table that computes the total of the field for which you want to chart a total.

               You can define a custom values value list that lists the months by name in January to December chronological order and then you can use it in a custom sort order when sorting by your text field with the month names.

               In your data source settings for the chart, you'll want to select the options for groups of data when sorted (Summarized data).

          • 2. Re: Chart modifying

                 Which custom value function do I use to list the months in chronological order in order to sort the months accordingly? 

                 I have about 2000 records for each month, and there is a total $ amount that comes with each record. I just wish to total these by month. Thanks for the help thus far! 

            • 3. Re: Chart modifying

                   Go to Manage Value list. Click New. Name your value list. Type in the month names in order into the custom values box.


                        I just wish to total these by month.

                   Are you sure that it's just by month and not by month and year? In other words, if you have data with the date 1/1/2014 and other data from 1/1/2013, will they be combined in the same month?

              • 4. Re: Chart modifying

                     you're right. I think perhaps I want to look at data by month each year, but perhaps I will just re-duplicate the layout in the following year and simply change the dates in order to demonstrate this. Although ultimately I will want to look at the data over the years but that is farther down the line. 

                     I ended up creating this but in my browse mode, in addition to the correct sets that have appeared, the overall numbers total keeps appearing repeatedly before the actual data I want to see. Any clue what is going wrong? 



                • 5. Re: Chart modifying

                       This is what keeps appearing in addition to the data I actually want to see.... 

                  • 6. Re: Chart modifying

                         But even the actual browse mode on a whole is not working out how I planned. Title not included but shows up with each of these pieces of data within browse mode 

                    • 7. Re: Chart modifying

                                but perhaps I will just re-duplicate the layout in the following year and simply change the dates in order to demonstrate this.

                           There's no need for that. You can perform a find to limit your found set to only records for a specified year and not need to make any new layouts or modify your chart design. The question I am asking is if you might need to chart more than 12 months of data at a time.

                           I am puzzled by your last two posts. They appear to have nothing to do with a FileMaker Chart. You appear to be trying to create a FileMaker summary report--which is a different thing.

                           Do you want a chart or a summary report?


                      • 8. Re: Chart modifying

                             Oh yes, sorry i am now trying to do summary reports. I thought that could be a better building block than a chart. I may need to chart more than 12 months in the future but not likely now 

                        • 9. Re: Chart modifying

                               There are significant similarities between the two, but also key differences.

                               For a summary report, you can add a Sub Summary layout part to your layout "When sorted by Month". You can place your summary field inside this sub summary layout part and it will show the monthly sub total. If you put the same field in the header, footer or a grand summary layout part, you'll get a grand total.

                               If you want only one line of data for each month, you can delete the body layout part. But keep in mind that sub summary layout parts will not be visible unless the "sorted by" field specified for them is included in the current sort order for your found set of records.

                               And you may want to use this more flexible method for grouping your records by month and year:

                               Define a calculation field, cMonth as:

                               YourDateField - Day ( YourDateField ) + 1

                          select Date as the result type. This computes the date of the first day of the month for all dates falling in that month. But a normal ascending sort will automatically group your records in Jan to Dec order and will automatically group by year as well as by month. You can use this field as the "when sorted by" field in your sub summary part and you can put the field in the sub summary part and use a custom date format to only show the name of the Month.

                               This can also be useful for charting your data by month and year.