9 Replies Latest reply on Aug 16, 2017 2:28 PM by ichiban

    Sub-summary in a date range


      I am working with truck peddles and specifying a date range.  I am trying to specify total dates for each peddle during this date range (i.e. if Iowa has 10 stops for July 1st, that would count as 1 date).  Each peddle will have several different stops of various weights.  I am trying to get an average of weight based on total peddle dates.  My table has an entry with all information for each stop for each peddle.


      For instance;


      Peddle Date Range - July 1st, 2017 - July 31st, 2017


      Peddle                 Total Peddles for Month            Total Weight       Avg. Weight


      Iowa                                        20                                  100,000               5,000

      Minnesota                               10                                   45,000                4,500

      Nebraska                                 15                                   60,000                4,000


      I am struggling with coming up with the total peddles during that month as well as the Avg. Weight.  Any help would be appreciated.

        • 1. Re: Sub-summary in a date range

          Sounds like a summary report with sub summary parts and summary fields that compute your total and average.


          Set up a sub summary part "when sorted by" state.

          Put the field for state and the summary fields that compute your total, average and number of stops in that sub summary part.

          Remove the body layout part.

          Perform a Find specifying your date range and sort the records found by state.

          • 2. Re: Sub-summary in a date range

            Thank you, Phil.  However, that is when working with reports.  I am doing a chart, so I don't have that much flexibility.  I am pretty sure I would be able to easily do this with a report.  However, I am just starting to work with charts.

            • 3. Re: Sub-summary in a date range

              What I suggested produces exactly what you asked for in your original post--which does not mention any chart.


              The value of a summary field can also be used in the y-series of a FileMaker Chart Object to chart sub totals.


              You still perform the find. You still sort by state to group the records. You'd use the "summarized data" data source option for you report.

              • 4. Re: Sub-summary in a date range


                I have a couple of questions:

                1. would you want to report on a random date range, like July 7 - August 10? Or would you stick with just a full month?

                1.5 Would you want to report on this month's data, or would you wait until the end of this month to report on this month?

                2. What kind of chart are you considering?


                What I'd do is create a summary table that stores this information and keeps it static. Last month's data won't (probably) change, so it is good stored. Your example above shows the exact fields you'd need, with a year and month fields as well so that you can do a search for a specific year and month.


                The kind of chart may need to be considered: you really need to have the same kind of data on one chart. Total and Average don't necessarily go together in a chart. I'd build three charts showing three different pieces of data: ihave a  chart to show, per state: the total # of peddles,  the total weight, the average. Each chart would have an x-axis of state and a y-axis of the one field in the summary table (if you go that route).


                I'll put together a small demo of charts built with a subsummary or with a summary table. I prefer the latter so I can keep fields static and don't have to do lots of sorting of records to get the correct summarization of data.


                A summary table is a good way to go, but it does require scripting that has to be run on the first day of the next month. That may or may not fit your needs.



                On another note, feel free to continue to ask more and more specific or clarifying questions here or in the space New to FileMaker. In that subspace, we don't mind if you need to continually clarify things as you post. Since charts are new to you, you may have further questions.

                • 5. Re: Sub-summary in a date range

                  Hey Jeremy,


                  First to answer your questions:


                  1.  The date range will be specified by the user and it can be anything, not only a full month.  Could be less, more or exactly a month.

                  1.5  The user will have access to run this script at any time.  Thus, they could be looking at the previous month or just look at last nights loads.  No consistency other than what they decide.

                  2. Currently running a bar graph, but that is flexible and I am willing to change, if necessary.  This was just an idea that I came up with to help better generate report.  Our CEO loves graphs, so I am just trying to accommodate him.


                  A static table is a possibility.  I may look at that option if I struggle too much with the initial problem.  Thank you for that idea.


                  You are correct about Total and Average not necessarily going together in the same chart.  I figured that out pretty quickly.  Plus, after the user saw that initial attempt of putting them together, he requested that we split them up into individual charts. Your idea of a third chart to show total # of peddles is valid, as well.  Didn't really thing about that, but a good idea.


                  I would love to see your demos of sub summary, if you have a chance to put that together.


                  For the record, I clicked on the "New to FileMaker" and there was a note on the bottom from Rosemary Tietge that says "This group is closing down".

                  • 6. Re: Sub-summary in a date range

                    The group that is closing down is the New User Group because we have replaced it with New to FileMaker. Because there is a link in the post, it shows up in the new place as well as where I posted it. It's something Jive does automatically.

                    • 7. Re: Sub-summary in a date range



                      Your answer to #1 has eliminated the possibility of a static table, in my opinion. That kind of table is suitable for storing descrete time blocks of information: Total sales in a month or a year or a quarter, not for the dynamic nature you need. So we go back to the subsummary idea.


                      philmodjunk's suggestion of using summary fields in the chart is the way to go. You'd create you summary fields to find the total and average of the weight (separate fields) and then use those in one chart after finding the records you want to display and then sorting them. That's one way to go. Alternatively you could use ExecuteSQL (my preferred method) and gather the data based on the dates a user puts into the start and end date global field. ExecuteSQL can do sums, averages, etc. Have you used that before?


                      Ill get you a demo in a bit, but i'm stuck on the # of unique dates for each state. As you describe even though there are 10 stops on a date for a state, that counts as one. A typical summary field wouldn't work in this case.


                      I'll get back to yah.

                      HEre's my demo so far,  but the # number of peddles is incorrect.



                      • 8. Re: Sub-summary in a date range

                        Word of caution:


                        If you use ExecuteSQL with the "group by" clause to produce the sub totals as delimited data, you may find that performance is slow. There's a knowledge base article that warns about this. Don't know if they improved on this for 16 or not.

                        • 9. Re: Sub-summary in a date range

                          Hey Jeremy,


                          Finally got the opportunity to implement your suggestion.  You are correct, the number of peddles is incorrect.  Nice presentation with all of the charts displayed in front of the user, though.  If you get a chance to work on the sub summary aspect, please let me know.


                          Thank you so very much for all of your help up to this point.