Grouping then counting only the unique values within that grouping
I'm trying to group by "Fund" then count only the unique values of the "Names" within each group. Is this possible?
It's possible. There are several possible methods. Some are kinda "out there" but work.
The most straight forward method is to set up a calculation using ExecuteSQL and the DISTINCT keyword to count unique values. A WHERE clause and the ? parameter can be set up to count distinct values for a given fund.
A non SQL method is to use "Sum the Reciprocal". It's complicated, but if you want to give it a try: How to count the number of unique occurences in field.
Another non SQL method is to set up a self join relationship to facilitate a custom values value list that lists only values for the current record's fund. then ValueCount ( ValueListItems ( Get ( FileName ) ; "valueListNameHere" ) ) will return a unique count.
Counting records in a related table with one record for each name linked by fund to the table shown would also return such a count of unique values and may be a data model that you already have or that you might find useful.
Okay so I opted for the ExecuteSQL method. Used Count (Distinct) in the expression. Problem is that running this with 4000 records takes about an hour or so to actually calculate. Is this normal?
ExecuteSQL can be slow to evaluate. As I best understand it, FileMaker is running the SQL code through an interpreter in order to evaluate the query.
I was looking at the link you sent for the "Sum the Reciprocal" approach. It calculates and counts the unique values but doesn't count for each group just the total of Unique Values. By using the same method with a tweak or 2 is it possible to show cows = 3, duck=1, dog=1?
Probably. I'd have to review the intricacies of that method to be sure...
okay so I think i may have not been clear on aspect... Each person may have invested in multiple investments. So the goal would be to group by fun then within that grouping count the unique names per that grouping. So John Doe may be listed 12 times but out of those 12 he may be in 4 groups, but within each group he may show up 3 times and I just want his name to be counted once for each group... If that makes sense.
That is what I understood from the beginning and while I am not fully sure, this sounds like something that can be done with sum the reciprocal as well as other methods.
Okay just wanted to be sure we were on the same page. I tried the executeSQL and unfortunately not getting the solution i was looking for and the fact it takes 2 hrs or so to complete makes it not practical to use. I'm going to try the "Sum the Reciprocal" though still not sure exactly how this will work. Any help you can offer would be great. Not sure what additional information I can provide though.
okay just noticed that the ExecuteSQL did actually do what was intended... though not sure how to have it show up in a repot. When it comes out it shows it up as shown below...
Retrieving data ...