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.
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.
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!