Michael T

I have been enlightened to the solution I needed. For those interested, please see this post: http://fmforums.com/forum/showtopic.php?tid/174795/post/197350/#197350 Thanks to those that helped.

I have a record for each customer for each month. For example, for customer A who has been active since 01/06 I would have 10 records Jan - Oct (since November is not complete). I have a master customer list in another table. In this table however is the monthly profit records for each customer.

Here's my situation: I've posted this on another forum and got zero valuable help. I hope to have better luck here... For purposes of Quarterly financial analysis I have several reports that summarize different fields in various ways. One of these is number of accounts. My database consists of profit records for each client for each month of the year going back a few years. One report I'm struggling with is to show how many Unique customers we have in each state for a given fiscal quarter. Since there is a record for each customer for each month, I want to count a customer if they have a record for any of the 3 months in a quarter, but don't want to count them 2 or 3 times if they were a customer for the entire quarter. Sample Data: Cust 1 Month 1 State CA Cust 1 Month 2 State CA Cust 2 Month 1 State AZ Desired Output: CA 1 account AZ 1 Account What I've done: I've created a value list based on a self relationship matching Customer ID and Fiscal QTR and used the following formula: ValueCount ( ValueListItems ( Get ( FileName ) ; "QTR CLA List by State" )) to get a unique count for each state. (CLA is our customer ID) The problem is that this only works when the found set is for the entire quarter and will display the same results in the report regardless of the found set. Also, I have about a dozen different reports that would need this type of calculation and I don't want to have to create a value list, self-relationship and a calculation for each one. Is there a calculation that can dynamically count the number of unique values in a field for a given found set? Any help would be greatly appreciated. I've searched the forum going back 6 months and could not find anything that provides a solution. If I've missed something, I don't mind being directed to the previous post. Thanks again.

I had the same problem with FM7. The only way I found to get my summary data into excel was to publish to PDF and use the copy table function (Acrobat Pro or Standard versions only) to copy the text into Excel. This was a Major pain! Not only could you not do this with the standard version of Acrobat Reader, but I could only do one page at a time. FM8 has built in functionality to export to Excel, which is the sole reason I upgraded. I hope for your sake that there is an easier solution in FM6, but I was never able to find one during my searches. P.S. I'm using OSX as well.

Okay, I've found a round-about way of accomplishing the quarterly report. What I had to do is create a calculation field (field/3) then a summary field to total the calculated field to generate quarterly averages for each region. This is cumbersome however because I have 4 items in the report to I would have to create 4 calculation fields and 4 summary fields on top of the 4 summary fields already created for the original report. In addition, when the boss asks for a 6 month and yearly report that means 8 more fields to create. There has to be a better way...

I'm having trouble getting my brain around how to create a report to show averages per quarter. Let me explain... I am tracking profit data for each customer each month. I have created a layout/report to show total profit data for all customers broken down by sales region. The next step is to take those monthly region totals and average them for the Quarter. Because of the way we track things I can't simply add the months totals. I can't figure out how to create a report that will average the monthly totals instead of the average of the individual records. I tried creating a self relationship based on the month field of the records but it didn't work. Is this a relationship problem or do I need a more complex calculation? Any ideas would be helpful, thanks.

The file name has changed. Try this one. If this does not work, e-mail me at fcsmarquee@sbcglobal.net

The file name has changed. Try this one. If this does not work, e-mail me at fcsmarquee@sbcglobal.net

The file name has changed. Try this one. If this does not work, e-mail me at fcsmarquee@sbcglobal.net

This is the file... It's pretty messy. The script for the repor tin question is called "Tier Ranking". What I think I really need to do is create multiple tables, but as I've said, I don't have a full understanding of relationships yet. Any advice would be much appreciated.

This is the file... It's pretty messy. The script for the repor tin question is called "Tier Ranking". What I think I really need to do is create multiple tables, but as I've said, I don't have a full understanding of relationships yet. Any advice would be much appreciated.

This is the file... It's pretty messy. The script for the repor tin question is called "Tier Ranking". What I think I really need to do is create multiple tables, but as I've said, I don't have a full understanding of relationships yet. Any advice would be much appreciated.

I have partially figured out how to solve my problem but have run into another one as well... I discovered how to add a Calculation field to my report and create a self relationship so that the calculations are sorted correctly (sort of). The problem I'm having now is that when I want to run a report on only a given set of records, the Calculation field in the report is still returning the calculation on ALL records instead of just the found set. Is there any way to get around this? Thanks in advance for any input!

I have partially figured out how to solve my problem but have run into another one as well... I discovered how to add a Calculation field to my report and create a self relationship so that the calculations are sorted correctly (sort of). The problem I'm having now is that when I want to run a report on only a given set of records, the Calculation field in the report is still returning the calculation on ALL records instead of just the found set. Is there any way to get around this? Thanks in advance for any input!

I have partially figured out how to solve my problem but have run into another one as well... I discovered how to add a Calculation field to my report and create a self relationship so that the calculations are sorted correctly (sort of). The problem I'm having now is that when I want to run a report on only a given set of records, the Calculation field in the report is still returning the calculation on ALL records instead of just the found set. Is there any way to get around this? Thanks in advance for any input!