Calculation - find fields results by month

[buckbuck]

I would like to define a field that will deliver a summary of all sales in a calendar month, those sales in the month defined by the Invoice Date field for the current record. This way I will see at a glance how my monthly sales are performing. Can anyone help? Thanks

[Lee Smith]

Do you mean finding a set of records for a month? Or are you trying to sort your report by month?

To find a set of records for a month, read about “Finding ranges of information” in your online help which is available when you have FileMaker open.

Do you mean finding a set of records for a month? Or are you trying to sort your report by month?

To find a set of records for a month, read about “Finding ranges of information” in your online help which is available when you have FileMaker open.

In June 2014

6/2014

From July 2014 through October 2015

7/2014...10/2015

That occur on a Friday

=Friday

From the 10th through the 16th of October or November 2014

{10..11}/{10..16}/2014

That occur on March 1st between 1868 and 1912 in the Japanese Emperor Year era of Meiji

m*/3/1

That occur on December 31st between 1930 and 1940 in the Japanese Emperor Year era of Showa

S{5..15}+12+31

[LaRetta]

Hi buckbuck,

Lee gave some ideas - here is how I would approach it:

In Invoices, create a summary field sTotalSale (which is summary of cTotalSale. Also create a calculation cMonthDate (result is date) with:

InvoiceDate - Day ( InvoiceDate ) + 1

This turns all your invoice dates into the first day of the month they are in. Then create a report which summarizes based upon this new date field. In your leading part, go to Inspector > Data and set the date display to April, 2012 for example. You will use this same date over and over in various ways - filtering (relational) portals and searching.

I wish I could find the original post - this concept came from Comment (no surprise). I use this concept repeatedly in everything I do.

[buckbuck]

Hi LaRetta

Thank you for your suggestion. I am familiar with your technique based on some very useful replies you gave to search techniques previously.

I am trying to create a 'dynamic' result. By this I mean one where the field on the layout shows a current balance (summary) for the month on the current Invoice's order date.

If the current invoice date or is 04/23/2012, I will see the summary for April 2012.

Your suggestion appears to be a report, as in a scripted search and result. Am I wrong?

Thanks

[LaRetta]

To aggregate, you usually need to look across all records. Create a self-join as:

Invoices::cMonthDate = Invoices 2::cMonthDate

Then the summary field (from Invoices 2), in this instance, will provide the results you want. You can also use a filtered portal but in invoices, it might be too large a stress; best to use relationship.

Added blue

Edited April 24, 2012 by LaRetta