Calculating Total Qty of items each month.

[gullfounder]

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.

 

The Files i am attaching has lots of other things. But i have putted all the related layout of the table in sub Folder called Misc

15-Mar-2014.zip

[No_access]

Create a new part , sub-summary  by name.. drag your fields in their and sort by name. and your done

[gullfounder]

Create a new part , sub-summary  by name.. drag your fields in their and sort by name. and your done

 

Yeah i already did that. It sort out every record by month and add the QTy too. But. If i want the record of current month or last month only! Then how can i filter it?

[eos]

But. If i want the record of current month or last month only!

 

Assuming that each line item is related to an Invoice (or other transaction) record, which has a date …

 

• either from the Invoices layout, find all Invoices for the desired month, then use the Go to Related Record script step (with the options Related records only…; Match found set) go to all related line items of all invoices in the found set, or

 

• from the LineItems layout, search for the desired month in the related date field from Invoices.

[No_access]

This is what I use

 

If(Year (Extend( ShipDate ))=Year(Get ( CurrentDate ));

Case(Get ( CalculationRepetitionNumber )=Month ( Extend(ShipDate ));

Extend(ItemPrice by ProductGroup2));"")

 

Note the use of EXTEND in a repeating field (12 reps)

 

 

[gullfounder]

 

This is what I use

 

If(Year (Extend( ShipDate ))=Year(Get ( CurrentDate ));

Case(Get ( CalculationRepetitionNumber )=Month ( Extend(ShipDate ));

Extend(ItemPrice by ProductGroup2));"")

 

Note the use of EXTEND in a repeating field (12 reps)

 

That is nice, can you send me sample file of it? I want to learn how you made it.

[No_access]

I can make you one, give me a day or so  and I will send to you. I obviously cant send you my actual database.

[gullfounder]

I can make you one, give me a day or so  and I will send to you. I obviously cant send you my actual database.

Ok sure no problem.

[No_access]

Here is your file, let me know what you think

test report.zip

[gullfounder]

Here is your file, let me know what you think

that was very impressive. I have few questions.

 

1) How will the report work for new year? For Example: If i have product A sold 15 on May 2013 and same product sold 35 on May 2014? will it show 50 in May column?

 

2) If i have 1000 products will it show all of them?

[No_access]

You are correct.  this report is designed for 1 year. on my system I need to go from 2011 to present. so I do 2 reports, I do a yearly one, exactly the same way except in years and not months, the user can toggle between years or months. just create a new sub summary part for years and a new calc and summary field.