running total based on date

[ridddder]

I am making a tool, for our sales manager to track sales, and contacts. I can't find my filemaker reference guide, and have searched the forum, and can't find what I need.

Basically, I want a calculation that will add up a total of all records based on the month the record was added.

For example, each record tracks the total number of contacts each salesperson has daily. Each record has an auto-stamp of what date the record was input. In the managers' layout, I want a field to show a running total based on the month. So the total contacts for each record of May are added together, but not the total contacts for June, and so on.

I can't figure out how to do it, thanks in advance.

Am making the database on a Mac, then porting it to Windows. Have both versions, Mac, & Windows of Filemaker Pro 5.5

[ridddder]

Wow, 41 views no replies....guess I stumped everyone on this one, I have seen much more advanced uses of filemaker from invoicing, to pos systems. Hmmmm, makes me wonder. I will check back later, am having issues with one of my websites, and php nuke.

I hope someone can help me, and thanks in advance if you can

~Doug

[Genx]

1) Create a stored calculation field called yearMonth

2) Calc = Month(CreationTSField) & "/" & Year(CreationTSField)

3) Create a new table occurance based on this same table.

4) Relate your primary TO with the new one via our yearMonth field.

5) Create a new calculation field called totalForMonth.

6) Calc = Count(OurNewTO::CreationTSField)

7) To be evaluated based on the initial TO.

If i misinterpreted anything (which i may have) please clarify.

Hope that helps.

[Vaughan]

"2) Calc = Month(CreationTSField) & "/" & Year(CreationTSField)"

If you reverse the calc and make it year/month it'll sort better. ;)

[Genx]

So it would. Probably be truer (that word just looks wrong) to its name as well ;)

[Chris C]

I was actually just about to post a similar question...

how would you add up a the total of a specific field in a certain record for a certain month?

Say every record has a number and a date... how would i get the sum of all the numbers for the month? I believe count only counts the number of records (right?)...

Thanks

[LaRetta]

Truly, I believe it works better to keep it a true date as referenced here

Then all you need to do is create a new layout - type, Columnar List with grouped data including subtotals (based upon this new date field). It will become a regular workhorse throughout your solutions.

You would perform a find for the period of time you wish.

You would have a report with this new date calculation in leading part. You would have summary field which is Total of 'that number' and place it in the same part as the date calculation. You would sort by this new date calc and go to previow mode.

[Genx]

Assuming you wanted to produce a report...

But on the other hand I will agree that an actual date field would come in handy in general, in which case change the calc in step 2 to:

Date( Month(CreationTSField) ;1; Year(CreationTSField))

...and set the result type to date.

I believe count only counts the number of records (right?)...

... right, but we are counting related records. In this case the related records are those with the same month / and year.

[LaRetta]

Say every record has a number and a date... how would [color:blue]i get the sum of all the numbers for the month?

Count wouldn't work here - you are right, Alex, it would count the related records or count those with a value in that number field. It appears to be a request to total the number in all those related records. But I could be wrong, I frequently am.

[Genx]

Well, I'm right for the first guy (i think)

Basically, I want a calculation that will add up a total of all records based on the month the record was added.

... but not for the second guy... (sorry)

how would you add up a the total of a specific field in a certain record for a certain month?

Instead of counting, we use Sum(OurNewTO::SalesTotals)

[LaRetta]

Yeah, it's difficult when someone else jumps onto a thread; it's easy to get confused on what kind of solution we're providing to which one.

BTW, just for additional clarification, your suggestion of: "... we use Sum(OurNewTO::SalesTotals )" would apply if we are using a relationship. One would use a summary field, Total Of "that number" if creating a report. Both approaches have their great points!

[Genx]

Yeh, we should probably clarify that we are in fact discussing two different approaches as it may not be clear to anyone who jumps in.

The first uses relationships to produce "live" totals visible in browse mode. The second uses summary fields to produce totals using sorting and preview mode (useful for reporting).

[Ugo DI LUCA]

The problem still is that the first guy was having php issues while running a fm5 version, so Table Occurrence would not have really solved his issue

Just kidding, true I was confused too about the two requests :