6 Replies Latest reply on Aug 13, 2010 1:22 PM by philmodjunk

    YTD calculation



      YTD calculation


      I am a SQL and MS Access whizz and am trying to convert to Filemaker.  One of the things I'm struggling with is calculating YTD sales by store and product category.  The table I have has sales listed by store, product category, and week ending date.  What I want is to create a report that summarizes the YTD sales by store and product category and I have no idea how to do this.  In Access it would be a simple summary by store and product category.  Any help would be appreciated for this Filemaker newbie.  Thanks.  

        • 1. Re: YTD calculation

          It's pretty easy to set up in Filemaker, though the approach is definitely different from how you'd do it with SQL and Access.

          You'll need what's called a summary report. This report can show individual items within each category and with sub totals or you can set it up to just have the categories with sub totals. You can break things down by month or not if you want.

          Here's a tutorial on summary reports. If you want just the categories and subtotals, look for the example near the end that describes deleting the body part from the layout.

          Known Bug List

          Note to make this a report for a specific year, you'd perform a find on the data to find all records with week ending dates in a given range such as 1/1/2010...12/31/2010 (dates are in MM/DD/YYYY format). Such a find can be performed by hand or in a script. (This is analogous to the WHERE clause in SQL)

          • 2. Re: YTD calculation

            Don't forget that, to find a entire year's worth of records, you can simply type the year into date field in find mode.

            • 3. Re: YTD calculation

              Thank you for the tips.  I will give it a try.

              • 4. Re: YTD calculation

                Ok I got it to summarize the data properly.  I have one summary field that totals the CURRENT YTD sales and another summary field that totals the PRIOR YTD sales.  I'm finding it difficult to calculate the percentage variance between these 2 summary fields.  I tried creating a calculation field but did not get correct results.  Any thoughts?  Thanks.

                • 5. Re: YTD calculation

                  I found my answer.  I needed to use the GetSummary function to calculate the variance between the 2 summary fields.  I'm not convinced yet that Filemaker is easier to use than Access though.

                  • 6. Re: YTD calculation

                    It takes learning some new approaches. Tricks that work with Access often are a bad approach and completely different methods are used filemaker so you find yourself having to relearn things.

                    I generally find that Filemaker enables simple tasks in a simple user friendly way such as finding all contacts in a contacts database that live in a particular town that are not simple at all in Access unless the developer specifically creates a tool for something which for FileMaker just works straight out of the box.

                     As the operations grow increasingly complex, however, the trade-offs between the two systems start cancelling each other out until the tipping point is reached where a decent SQL expression becomes simpler to implement than Filemaker's "Query by example" paradigm.