Jump to content

Calculation to count number of unique values


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

Recommended Posts

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5786 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
 Share

×
×
  • Create New...

Important Information

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