Count Unique Value for Report

[jasongodoy]

I have a Line Items table that is related to my Customers table through the Invoices table. What I'm trying to do is Count the unique "customer_id" values that my Line Items table might have. For example, if Joe Smith has an invoice that has 2 Line Items. In my line items table, there would be 2 records for Joe Smith. If I did just a simple Count on Joe Smith's "customer_id" from the Line Items table, I would get 2. What I'm trying to figure out is how to Count Joe Smith once (or unique). I hope I explained this right. Please HELP!

Thanks in advance,

J

[LaRetta]

Hi J,

From LineItems, try a calculation (result is number) Count(Customers::CustomerID). Since it will be looking UP the relationship from LineItems, only related Customers will be counted. There are several approaches here and much depends upon how you are finding/filtering LineItems; and whether you want a report, portal etc. Is this based upon found set in LineItems? Or based upon pre-entered global date ranges?

Well, another thing which would work is to create a value list based upon Customers, using values from field - only related values (and select Customers). Then a simple: ValueCount(ValueListItems(Get(FileName) ; "yourNewVL") )

Again, it would help to know how you need to pull this process into your existing solution.

LaRetta

[jasongodoy]

LaRetta,

Thanks for the quick response! To answer your first question, yes this is on a found set of items. Basically my ultimate goal is to have a report showing the amount of unique customers by state. If a customer has multiple items, currently they are counted in my "s_customer_count" Summary field multiple times. Joe Smith might have 1000 Line Items associated with him over the life of this database, but I want his unique "customer_id" to count only once instead of 1000. I have the Report working correctly, with the exception of counting only the unique values of the "customer_id" field once.

Thanks,

J

[LaRetta]

If you need this strictly for reporting based upon a found set (sub-sum report) then I wouldn't feel comfortable giving a suggestion. Most of my requirements are handled through relationships and I haven't needed to create a report based upon this kind of 'unique.' Possibly Get(NthRecord) could be used here to grab the CustomerIDs of the found set. I haven't used Get(NthRecord) yet (I just moved up to 8).

In other words, I wouldn't trust my judgement on the best way to approach such a report and I hope others (who create these types of reports) steps in and make suggestions. However, if you aren't helped by others here, I will come up with something for you. :wink2:

[jasongodoy]

Thanks for the honest answer LaRetta. Hopefully someone will know how to figure this out.

[comment]

Basically, you want to count how many subgroups (Customers) are in a group (State). This is a bit tricky in a report, but possible. You need to add the following fields (in LineItems)

sCountItems (Summary, Count of LineItems SerialID field)

cInvCountByCustomer (calculation, unstored, result is number) =

1 / GetSummary ( sCountItems ; CustomerID )

sCountCustomers (Summary, Total of cInvCountByCustomer)

The final field, sCountCustomers, when placed in a subsummary by State part, should give you the number of unique customers in each State, and when placed in a grand summary, the number of unique customers in total.

You need to sort your line items first by state, then by CustomerID.

[Fenton]

I think the answer is to not use the new fancy tools, but to do a Loop on a sorted found set and count them.

I tried it the fancy way, with both ValueListItems and a GetUniqueValues Custom Function. Both of them work, to a degree, but there are 2 fairly serious problems. The first is that they are slow, like real slow with large record counts.

The 2nd problem is that they tend to want to work with either a relationship or a copied/pasted set of IDs. Neither of these methods is very flexible when it comes to dealing with a found set of mixed states.

The Loop is fairly speedy. It is scripted however. And it needs somewhere to put the totals (as would anything). I put them into a States table, as that seemed the most obvious place.

(P.S. I don't know how I got 58 states. -)

InvoiceCustomers.fp7.zip

[Fenton]

Yep, comment's got the method. It's an old one, but manages to still confuse me -]

[Razumovsky]

I would tend to trust comment much more than me in general, but I was thinking the old max trick might be slightly more elegant:

Fields In LineItems:

cUniqueCustomer (unstored number calc) =

Max(Customer::CustomerID)=LineItems::Customer ID

sUniqueCustomer (summary field) =

Total of cUniqueCustomer

This should work as a subsummary or summary depending on layout part placed on.

-Raz

[comment]

Both methods are summarizing an unstored calculation, so there's not much hope for elegance here. Your method can be improved slightly by changing:

Max(Customer::CustomerID)=LineItems::Customer ID

to:

Customer::CustomerID = LineItems::CustomerID

This will also return true only once per group, so the Max() calc is not required.

However, I started with the assumption that there's no CustomerID field in LineItems (indeed, why would it be there?).

[Razumovsky]

Good point about the max function. Is that an artifact of my FM 6 mentality, or has it always been redundant?

Yes, customer ID shouldn't be in lineitems. It can be called through a relationship though:

Invoice::CustomerID=Customer::CustomerID

Seems to work for me. We are splitting hairs about elegance here, but I always tend to learn something in these type of discussions...

-Raz

Edited March 7, 2006 by Guest
typo

[comment]

It was always redundant.

However, there's another problem here. I should have noticed this right away, but being familiar with the method you suggested, I didn't give it more than a glance. Now, with your latest modification, it strikes me much more clearly. From the context of LineItems,

Invoice::CustomerID = Customer::CustomerID

is always true.

[Razumovsky]

Yeah, that was my initial thought when I read your first reply.

Customer::CustomerID = LineItems::CustomerID

should always be true as well in the line items file (which was why I used the Max). But as that returned only once per group when I tested it, I tried the:

Invoice::CustomerID = Customer::CustomerID

and it returned only once per customer.

I admit that I dont quite get why it works without the max function, but it works...

-Raz

[comment]

Do you have a demo?

[Razumovsky]

Well,

its a rough quickie and uses only one table, but the relationship structure is the same.

In this case

Actors=Customer

Directors=Invoice

Movies=Items

cDirectorRelate is analagous to

Customer::CustomerID = LineItems::CustomerID

cActorRelate is analagous to

Invoice::CustomerID = Customer::CustomerID

-Raz

(yes, I realize this relational flow does not make sense in the context of a movie tracking DB, it was just the file I had open and used for testing...)

MovieLib.fp7.zip

[comment]

A flat file is NOT the same as 3 separate tables. If you want to do it with self-joins - why that's what LaRetta suggested from the start.

[Razumovsky]

Yes, I realize that a flat file is not the same. I was taking a shortcut to illustrate a point, which ended up confusing me and the issue...all clear now.

I was thinking of SelfJoins, but don't see that in LaRetta's post (are you talking about her Value List suggestion?)

Thanks for forcing me to work this out clearer. After slogging around, I was only able to accomplish it using 2 self joins and a calc field:

Invoice::Invoice ID= InvoiceJoin::Invoice ID and Item ID = LineItemJoin::Item ID

I seem to remember having performace and updating issues with techniques like the valuelist method in large files. Perhaps the doublejoin might be smoother than the value list, but I like your 3 field - no join solution the best.

I attached a bit more relevant file working off the timebilling template.

Employees=Customers

TimeBilling=Invoices

LineItems=LineItems

Thanks for keeping me on my toes.

-Raz

Time_Billing_Unique.fp7.zip

[comment]

I am sorry - I should have been more clear. I meant LaRetta already suggested using relationships, but since we need to consider the found set, this won't work. In your demo, omit the first record of any employee. The count goes down, but other instances of items with the same employee remain, so the result is wrong.

[Razumovsky]

but since we need to consider the found set

Details, details...

[Razumovsky]

comment,

Not to harp on the issue, but I really liked your 3 field solution and thought I understood it.

sCountItems (Summary, Count of LineItems SerialID field) - straightforward

cInvCountByCustomer (calculation, unstored, result is number) =

1 / GetSummary ( sCountItems ; CustomerID )

divide 1 by the number of present items sharing the same customer as this item (using customerid as a breakfield)

sCountCustomers (Summary, Total of cInvCountByCustomer)

Total this value across all present records, returning the total number of unique customers represented in the found set.

However, when I tried it out, I encountered a problem. As you reminded me, customer ID should not be in the line items file. No big deal I thought, just adjust cInvCountbyCustomer to:

1 / GetSummary ( sCountItems ; Invoices::CustomerID )

But, this does not return any value when sorted by Invoices::CustomerID. It seems like the GetSummary function won't accept a related field as a break field, but I can not find this documented. Is this true, or am I still confused?

I attached a file with my attempt.

-Raz

Time_Billing_Unique2.fp7.zip

[comment]

OMG. Of all things, this is one I haven't thought to test - even after saying a flat file is not the same... I believe there may be a jinx on this thread.

It looks like you're right, and that there needs to be CustomerID in LineItems after all (luckily, it can be an unstored calc). I don't see it documented either.

[jasongodoy]

WOW! I had no Internet access for a day and this post has exploded.

Thanks everyone for all the helpful suggestions!

[Razumovsky]

I believe there may be a jinx on this thread.

Yes, lots of almost correct ideas here! I just hope folks manage to read all the way through. I think I will post this issue as a new thread.

-Raz