For context, monitoring product listings on Amazon.
I have 4 tables:
- Brands, with a lookup field to ASINs, from Catalog
- Catalog (containing ASINs), with a lookup field to Offers
- Offers, showing every offer on each ASIN, where each offer has a Seller ID, a lookup from Storefront Profiles.
- Storefront Profiles: Contains Seller ID.
I'm trying to count, at Brand-level, the number of unique sellers associated with their ASINs. I've tried all sorts of counts and lookups, but can't get the right number. Either counting, duplicates, spaces.
So far I have:
- In Catalog: A linked record to Offers, showing all offers per ASIN, with a lookup to show the active sellers.
- In Brand: A linked record to Catalog, showing all ASINs per brand, with lookups to show the offers and active sellers.
- Brand - 'Active sellers' - shows unduplicated sellers. Using
- COUNT(ARRAYUNIQUE({Active Sellers})) in a formula gives me 0.In Brand: 'Sellers' - A rollup of Active sellers using
- ARRAYUNIQUE(ARRAYFLATTEN(Active Sellers)) shows a comma separated string.In Brand: '# Sellers" - a rollup of Active Sellers from ASINs (a lookup from catalog) - usingCOUNTA(ARRAYUNIQUE(values)) gives me 48.The actual number of unique Seller IDs in Offers is 25.
Any ideas? I've tried pretty much everything the internet has offered. Anyone here able to help?!