We use a customer relationship management system where both sales and customer service people are identified by 4-letter codes ("JSMI") and the actions are identified in another field with 3 letter codes (e.g. "SLM" - Sales left message).
There are a whole bunch of 'many-to-many' relationships here, which QV seems to have difficulty with. A customer contact ("Mary Jones") can be contacted by one or more sales people, and also one or more customer service people, depending on what's going on. She could be contacted by any of
these people multiple times in one day (e.g. sales rep called and left two messages before reaching her that afternoon). So, a potential record set for her
could look like:
Date UserID Action Contact
5/29 JSMI SLM Mary
5/29 MBRO CLM Mary
5/29 JSMI SMC Mary
5/29 CJON CSP Mary
5/29 CJON CES Mary
5/30 MBRO CMC Mary
5/30 JSMI SES Mary
etc.
My problem is to find specific dates associated with specific actions, such as "When was the last time sales made contact with Mary?", or to find how many times an action has occured e.g. "How many times did we try to contact Mary in the last three months?".
I've tried formulas like "=count(aggr(if(Action='CMC',Action),Contact))" or "=if(Action='SMC',aggr(max(Date),Contact))'
and many variations thereof, but nothing seems to work.
Surely I'm not the first person to have this problem! Any ideas out there?
