Set Analysis, non-visited customers (table)

[ramzi-manoubi]

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.

[sunny_talwar]

Would you be able to share your qvf to take a look at?

[ramzi-manoubi]

I can't share the data loaded in the qvf. Do you want the qvf without data?

[sunny_talwar]

I was thinking to test out, but without data, not sure how would I be able to test out

[ramzi-manoubi]

I understand, but I don't own the data.

I'll look first myself some more and otherwise I'll prepare dummy data.

Thank you for your involvement so far. You're an angel.

[ramzi-manoubi]

With this formula, why do filters still have effect?

Derived Dimension:

=aggr(

    only({1-1<Visit_Id={"*"}>}

        Cust_Id

    ),

Cust_Id)

[sunny_talwar]

Because this is a dimension.... if you want to ignore selections, add this same set analysis in your expression.....

[ramzi-manoubi]

Using it in the expression also doesn't work.

I'll make an effort to illustrate the dillema with dummy data, tomorrow.

Visits have a date and a customer associated to them.

Customers without a visit have not a date associated to them.

By filtering on year, only customers with visits remain.

I want to have a list with the customers who were not visited in 2016.

[karthiksrqv]

Hi,

Do you think this will work?

=if(aggr(if(isnull(max(VisitDate)),True(),False()),CustomerID),CustomerID)

along with a

=sum({1} <metric field>)

[ramzi-manoubi]

Thank you for you comment. Would normally work, but there's a problem with filtering on date.

I'm testing something that's kinda working right now. When I'm done, I'll share the idea behind it.

[karthiksrqv]

Sure.

I believe it should be a combination of tweaking BOTH the dimension and the metric involved.