4 Replies Latest reply on Mar 17, 2014 1:13 PM by philmodjunk

# Calculating Total Qty of items each month.

### Title

Calculating Total Qty of items each month.

### Post

Hi, I am getting an issue regarding Calculating the Total of Each individual Item per month. I have created  2 tables in FileMaker. A Item table which will have ID and Date. Another table LineItems. which will be related to Item with via Items ID as FK in LineItems.

http://imgur.com/vdbmhEA

I wanted to create a List of items per month based. and there Total QTy per month. Something like that.

http://imgur.com/qUWXF8v

Of course its showing all the Items sum. I want it to have sum on each individual item i have that month. For Example: Marks are are repeated 2 times in second image. it shouldn't have only it showed once and its total sum.

• ###### 1. Re: Calculating Total Qty of items each month.

Will there be multiple records in items for the same item each month? Assuming that the answer to that question is yes....

First define a calculation field, cItemCount, in Items: Sum ( LineItems::Quantity )

Then define a summary field in items that computes the total of cItemCount.

Also define cMonth as a calculation field returning a date: Date - Day ( Date ) + 1

This returns the date of the first day of the same month to provide a common value for grouping your records by date.

Use  List view layout to show the records from Items. Replace the body of the layout with a sub summary layout part "when sorted by" id. Put the summary field in this sub summary layout part plus any other fields from items that you want.

Sort your records first by cMonth and then by id to properly group your records for this summary report.

• ###### 2. Re: Calculating Total Qty of items each month.

Thanks PhilModJunk. That helped a lot.

• ###### 3. Re: Calculating Total Qty of items each month.

One more thing, The cMonth field will have year stored right? Otherwise won't it will start adding the last months records of the same month?

• ###### 4. Re: Calculating Total Qty of items each month.

It's a date field so it includes month, day and year, but the value for day is always 1. And thus this will not combine data from the same month but different years.