I have a list of clients and their purchased items. Each sale is its own line item. Each client gets to me by several different referral sources. In my summary table I have each referral source and each state. For ech source and state I can easily get Total sales in dollars and the amount of sales (items). What I don’t know how to calculate is how many customers made up the total sales as some customers purchase several items.
In doing some research it appears I am looking to calculate how many distinct customers I have in that group.
I’m struggling to come up with something. Of course, I can pull another summary table by Customer, Referral Source and State and then add them up manually, but that is not very “Gristy” of me.
Let me know your thoughts.
Hi Jordan!
I created an example for you here:Count of Distinct Value in a Summary Group - Grist
The formula in my example is len(set(r.Customer for r in $group)).
- $group is the list of records being summarized in each sumamry row (grouped by state and source).
- r.Customer is
the field that contains the customer name (the field you want to count distinct values of)
- set() is a Python method that lists distinct elements in a list. It will return all the unique Customer names in $group
- len() then counts all the items in the set list.
I highlighted row 5 as a quick reference. 6 total sales, all attributed to 1 customer.
1 Like
This seems to have worked. I tested it on a small data set so Ill pull a bigger one and see if it matches what I did manually for last month.
@anais-grist you are the bomb!
1 Like