Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've been spending quiet some time now trying to figure this out. Any help is welcome and very much appreciated.
Context:
CRM, there are customers, there are visits.
I would like to have a list with all customers for which there are no visits registered.
I created a table with only measures as columns, except one.
For the dimension column I use following formula:
=If(
aggr(
count(
Visit_Id
),
Year,Cust_Id,...
) = 0,
Cust_Id
)
In this column I show the Customer Id, but only when the count of visits = 0.
This seems to work in some cases, but is giving me a hard time when I filter.
Thank you for your time.
This is the data:
Cust_Id Visit_Id Year
----------------------------------
Cust1 Visit 2016
Cust2 Visit 2016
Cust3 Visit 2016
Cust4 Visit
Cust5 Visit
I want the Customers without visit in 2016.
After applying filter on year = 2016
Cust_Id Visit_Id Year
----------------------------------
Cust1 Visit 2016
Cust2 Visit 2016
Cust3 Visit 2016
Only those with visits remain, while I want those without
My solution
Cust_Id Visit_Id Year
----------------------------------
Cust1 Visit 2016
Cust2 Visit 2016
Cust3 Visit 2016
Cust4 Visit
Cust5 Visit
Add this data:
Cust1 1899
Cust2 1899
Cust3 1899
Cust4 1899
Cust5 1899
Filter on 2016 AND 1899:
All customers remain in the selection.
And now I can see for 2016 which customers have no visits.
Not the most pretty solution, but the only thing I can think off.
In the attachment I added a dummy QVF illustrating the problem.
If anyone could take a look at it, that would be really great.
Thank you.
It has been sometime and I am not completely sure what you want, but check the attached
Thank you for the help. With this example I see the rows I want to see. Can the same effect be achieved without an extra column?
The report is intended to check up on the sales team. It's more interesting to see which customers they did not visit, than those they did.
Check the attached
Thank you very much.
You've been an incredible help to me.
No problem at all... its usually easy to help when we have a sample to look at... otherwise it is always a guesswork...
Best,
Sunny