1 Reply Latest reply on Jan 4, 2016 4:28 PM by mikebeargie

    How to make a year-to-date report with multiple previous years?


      I would like to make a report and/or chart that shows year-to-date (YTD) totals for each month with the months listed in rows and the current and last three years listed in columns with a grand total for each YTD at the bottom.  Can this be done?  I currently make a report that shows the monthly YTD subtotal for each year by itself, but I would like to have a side-by-side comparison of the current year with the past three years. Thanks!

        • 1. Re: How to make a year-to-date report with multiple previous years?

          It can be done. However you will need to become familiar with the virtual list technique. Where you gather your values up into global variables, then use calculation fields to display those values.



          -create a "reports" table,

          -create a field, numeric type, called ID.

          -create a field called "Column 1", calculation type, unstored, with the calculation of: GetValue($$column1; ID), result as text.

          -create 12 records in the reports table

          -replace the contents of the ID field with serial numbers, 1-12.

          -write a script that uses the Set Variable script step, to create $$column1, with a calculation of:

          List("Jan."; "Feb."; "Mar." ; etc.. )

          -run the script.


          You will now see that the column1 field in your reports table is showing the values for the months you entered in the $$column1 variable. This is essentially virtual list technique.


          Essentially you will need to create fields for each of your columns (EG title, year1, year2, year3), then populate the corresponding global variables with values for your comparison.


          It's "virtual" because your values are in global variables, which are stored in memory, as opposed to actually records.


          This is the best method for grid style reporting I've found. It has a bit of a learning curve but once you figure it out it's easy enough to reuse. If you google "filemaker virtual list technique" there are plenty of training resources.