Calculation to count number of unique values

[Michael T]

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.

Edited November 27, 2006 by Guest

[Genx]

Hold on so you have x customer records for x months

or you have 1 customer record and x months for that customer?

[Michael T]

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.

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