Help Calculating Earnings Over Time

[DaveHamilton]

I hope I'm posting this in the right place, and now I'll get right to the point.

We track payments for ads sold over a period of time on different web sites. Right now I can create a report that shows me that Site A earned $3100 for Campaign X that started on 5/23/2008 and ran through 6/22/2008. If I order based on start date that would show up in May's report, but really those dollars aren't all attributable to May. The $3100 is for the entire period, meaning they earned $100/day.

I would like to generate a report that would show

May:

Campaign X $900

June:

Campaign X $2200

Given the data I have, I need to find the number of days in the period (easy -- subtract start date from end date), divide the total earned by that amount (also easy) and then the hard part that has had me scratching my head for weeks: how do I go about breaking that out such that a report is made showing that particular Campaign X multiple times (twice in this instance, for May and June, but also possible a campaign could have a start date of 3/15 and end on 6/14, so three months, etc).

I'm guessing a temp table of some sort is in my future here? Any ideas? Help, please?

[comment]

Strictly speaking, you'd need to generate a RECORD (in a temp table) for each month/campaign, then base the report on that table. However, if you generate a report for one month at a time, you could calculate the proportional amount of each campaign for the given month, and include only this amount in your report.

Why isn't the report based on actual payments made, and the dates they were made on?

[DaveHamilton]

Thanks so much for your reply!

Strictly speaking, you'd need to generate a RECORD (in a temp table) for each month/campaign, then base the report on that table.

Ahh ok. Is there a resource that explains "temp tables" somewhere, or perhaps a link to some examples? In all the years using FM, I've never made use of temp tables. I realize it's more of a hack than an official FileMaker construct, and want to make sure I'm implementing it in a "best practices" kind of way.

However, if you generate a report for one month at a time, you could calculate the proportional amount of each campaign for the given month, and include only this amount in your report.

Hadn't thought about one month at a time. That's an interesting idea, and could certainly be done. Still I'd need a temp table, though, right? Otherwise how would I pull out just the one (correct) month worth of data?

Why isn't the report based on actual payments made, and the dates they were made on?

That's one report, for sure, and already exists. But that only details cash flow dates, not earnings dates. Let's say an ad runs between May 15 and June 15, but gets paid on July 27. The cash comes in for July, but the *earnings* are in May and June, respectively, and that expecially matters if is using accrual method of accounting.

-Dave

[comment]

Hadn't thought about one month at a time. That's an interesting idea, and could certainly be done. Still I'd need a temp table, though, right?

I don't think so, but I may be missing some complexity. Say Campaign A ran from Jan 1, 2008 until Feb 15, 2008, and earned $2,300 in total. If the report is for February 2008, then we know the campaign ran for 46 days in total, and 15 of those were in February, so:

2300 / 46 * 15 = 750

is the reported sum for this campaign.

I've never made use of temp tables. I realize it's more of a hack than an official FileMaker construct

Temp tables are just like any other table. Their being temp is purely a psychological condition - Filemaker doesn't know that.

[DaveHamilton]

Say Campaign A ran from Jan 1, 2008 until Feb 15, 2008, and earned $2,300 in total. If the report is for February 2008, then we know the campaign ran for 46 days in total, and 15 of those were in February, so:

2300 / 46 * 15 = 750

is the reported sum for this campaign.

That's exactly right. But the calculation would have to happen in a script, populate a field, and then run the report. Wouldn't that dictate using a temp table into which to populate that field? Otherwise how would the report "know" that I want February's data versus any other month?

[comment]

There would be a global field for the reported month (this could be a Date field, with ANY date in Feb 2008). An unstored calculation field will calculate the reported sum for each record, based on the global anchor date.