Jump to content

counting unique values using "min(serial)="


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

Recommended Posts

Hi,

Here's the premise:

I want to generate a report of projects for a specific date range. I want to be able to count the number of unique professors who had projects during that date range. The total number of projects will be greater than the total number of unique professors, ie, there will be mutiple projects to one professor.

Currently, I import the data into an empty table and use the following calculation that I got from the FM site to tag records as having a unique professor or not. I created a clone of the table and self join relationship as instructed on the FM site http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

min(serial)=min(ProjectsClone::serial)

this returns 1 for the first instance of a professor and 0 for subsequent instances. I then tally this field to get my total number of unique professors.

Here's the problem. When I do a query for the date range I want, my uniqueness tagging is not updated for the subset. I have to delete the omitted records in order to update the uniqueness tags. Is there a better way for all this? Mind you, I need the uniqueness tag to summarize other things, eg, if(uniqueProf=1 and dept="Chemistry",1,0)

I'd like not to delete the omitted records and somehow have the uniqueness field be dynamic and update with found sets. Frankly, I'd like not to have to do the import into new table thing but perform all this on the original table.

Thanks

Link to comment
Share on other sites

Have you tried using a summary report for this?

Can you describe the data structure, because (unless I have missed something) this shouldn't be too difficult to do. Certainly not requiring imports into empty tables, etc.

Link to comment
Share on other sites

I created a clone of the table and self join relationship as instructed on the FM site http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-of-unique-values-in-a-field

As you have discovered, the self-join is not a good method because it disregards the found set. The second suggestion looks more promising, but I couldn't follow it. See if this helps:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__p__289204#entry289204

Link to comment
Share on other sites

This topic is 4307 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.