Jump to content

running total based on date


This topic is 6093 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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. :wink2:

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!

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 6093 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.