9 Replies Latest reply on Apr 14, 2013 4:44 AM by OmarFahmy

    Sales comparison previous year



      Sales comparison previous year



           I made a database with the following tables:

           Orders, Customers, Suppliers

           I have a field Orders::TotalAmount that calculates the total amount per order and a summary field (Total of TotalAmount) and I need to create a report to display 2 years total amount orders per customer next to each other for comparison.

           Can someone help me with that ? 


        • 1. Re: Sales comparison previous year

               Are you familiar with summary reports?

               Using sub summary layout parts, you can easily produce a report with this format:

               Customer A
                   2011 totals:   $3,000
                   2012 totals:   $2,225

               Customer B
                   2011 totals:   $3,035
                   2012 totals:   $2,885

               And so forth....

               You base the layout on Orders and put the Customer fields in a sub summary layout "when sorted by" Customer ID. You then must sort your records by that same field. You can perform a find to pull up orders for any number of different time periods for such a report.

               A side by side comparison with the totals in two columns are also possible, but it takes quite a bit more effort to set up so I am suggesting the simpler option first.

          • 2. Re: Sales comparison previous year

                 Thanks for the comments ..

                 I already did the simple answer and is working perfectly in my current database  .. what I need is the side by side comparison because this is the default format requested as a report. 



            • 3. Re: Sales comparison previous year

                   Presumably, you have this relationship:


                   Customers::__pkCustomerID = Orders::_fkCustomerID

                   Though your field names may be different. (See: Common Forum Relationship and Field Notations Explained if this notation is unfamiliar.)

                   If so, you can make your report on a layout based on Customers. Put a one row portal to Orders on your layout.

                   Give it this portal filter expression:

                   Customers::gYear1 = Year ( Orders::OrderDate )

                   Place your summary field inside the portal row. Define a number field with global storage for use with this expression.

                   Make a copy of this portal and its summary field to place next to the first portal. Change it's filter expression to be:

                   Customers::gYear2 = Year ( Orders::OrderDate )

                   Now you can enter a year into gYear1 and a different year into gYear2 and you can then get yearly totals in two columns.

                   There will be one possible glitch, however: When you first select values for the global year fields, the portals will not automatically update to reflect the change in these values until you either use Refres Window [ Flush cached Join results] in a script or modify your relationship to be:

                   Customers::__pkCustomerID = Orders::_fkCustomerID AND
                   Customers::gYear1 X Orders::anyfield AND
                   Custoemrs::gYear2 X Orders::anyfield

                   It does not matter what field you select in Orders for these two added pairs of match fields. This relationship forces the filtered portal to update automatically each time you modify a value in either gYear1 or gYear2.

                   If you are using FileMaker 12, there are also ways to get these two yearly totals using calculation fields with the ExecuteSQL function.

              • 4. Re: Sales comparison previous year

                     But this will give me the information I need for each customer seperately (per customer record)

                     How would it work if I need the final layout to be :

                                                                             Year1                               Year2

                     Customer1                                 Value                                    Value 

                     Customer2                                  Value                                   Value



                • 5. Re: Sales comparison previous year

                       Yes. It will give you what you have specified. Use a list view layout so that you can list each customer record in a single row. Where you show "value" is where you'd place the one row filtered portals.

                  • 6. Re: Sales comparison previous year

                         Thanks a lot for your valubale help !! :-) .. 

                         Just one more thing .. Is it possible to compare the years through calculation .. like for example percentage difference between years for each customer ?? 

                    • 7. Re: Sales comparison previous year

                           Not with a filtered portal and these relationships.

                           But if you defined these relationships:

                           Customers::__pkCustomerID = OrdersYear1::_fkCustomerID AND
                           Customers::gYear1 =  OrdersYear1::cYear

                           Customers::__pkCustomerID = OrdersYear2::_fkCustomerID AND
                           Customers::gYear2 =  OrdersYear2::cYear

                           OrdersYear1 and OrdersYear2 would be new occurrences of the Orders table.

                           You can replace the filtered portals with just the Total of TotalAmount from OrdersYear1 and OrdersYear2 to show the totals for each year.

                           Now this calculation defined in Customers could give you a precent increase from year 1 to year 2:

                           (OrdersYear2::Total of TotalAmount - Orders::year1::Total of TotalAmount ) / OrdersYear2::Total of TotalAmount

                      • 8. Re: Sales comparison previous year

                             That's great ... Thank you .. I've been struggling with that for a week now ..

                             I really appreciate your help and time 

                        • 9. Re: Sales comparison previous year

                               Hi Again,


                               I've worked up the above solution and it's been working fine. I'm facing another problem now when trying to compare the values from the 2 years on the same chart by month. The chart only relates the values to one table ( either year 1 or year 2 ) while the other one is fixed over the months. 

                               The X-axis is based on Monthname(ordermonth) and the Y-axis has 2 series one for each each year (from different tables)

                               Can you please help me out with that?