4 Replies Latest reply on Feb 3, 2014 2:27 PM by vijaybhanabhai

    Month & year wise sales

    vijaybhanabhai

      How can i display Month/year sales in a layout. I have 3 years sales records & wish to display each months sales summary on layout. eg summary for 2014 Jan , 2012 Dec, 2012 Nov......upto... 2012 Jan.

        • 1. Re: Month & year wise sales
          mikebeargie

          http://help.filemaker.com/app/answers/detail/a_id/6198

           

          You need to make a list view for your report, and create a subsummary layout part that will house your totals. Once you've built the layout, sort based on the field you are subsummarizing by, and the report will change to be grouped together by your subsummary.

           

          You will most likely need to calculate a "YearDate" field to subsummarize by. It should be something like = Year(yourdatefield) & month(yourdatefield).

          • 2. Re: Month & year wise sales
            vijaybhanabhai

            I actually have a report which shows the sales for the date range. I need to insert in this report the sales for each month of the year at the bottom of the report. I beileve i have to somehow create a field which will capture the sales for each of the previous months so the field can be inserted into the said layout. Can you help please.

            • 3. Re: Month & year wise sales
              mikebeargie

              You can setup a calculation field using the ExecuteSQL() function with a "Group By" parameter. It's a complicated function that is covered very well here:

              http://www.filemakerhacks.com/filemakerhacks/6406

               

              Something like:

              ExecuteSQL(

                  SELECT SUM(amount) FROM yourtable WHERE yourdatefield BETWEEN ? and ? GROUP BY yearMonth"

                 ; "" ; "" ; reportStartDate ; reportEndDate

              )

               

              Alternatively, you could use the virtual list technique to gather the summaries into a global variable (IE $$reportSums) and then merge that variable into a text box in your layout (<<$$reportSums>>). The virtual list technique is covered here:

              http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

              • 4. Re: Month & year wise sales
                vijaybhanabhai

                I am not a experienced user and have never used Execute SQL. Would really appreciate if you can show me the steps for fileds under:

                 

                I have the following fields:

                 

                Profit Extimate: Total  Invoice Amount - Sum ( Line Items::Tkt Cost ) - Sum ( Line Items::Tkt Tax )

                Sum Profit Extimate: Total of Profit Extimate

                Month: MonthName ( Sale Date )

                 

                I need a field to display on layout the Sum profit Extimate for each Month. eg. for Jan 2014, Dec 2013, Nov 2013 etc..

                 

                Thanks