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]

What exactly are you filtering on?

[ramzi-manoubi]

Thank you Sunny for you comment.

I filter on classification fields of the customers and on year (2016).

If more clarifications or info is needed, please ask.

[sunny_talwar]

So what exactly goes wrong when you make selection in customer and/or year field? Can you show by the means of screenshots or a sample?

[ramzi-manoubi]

Sorry for the late reply. Tuesday I've to do the support.

I added 2 screenshots clarifying the challenge.

In the table I want only the Id's for which there are no visits registered. I tested some more and filtering on target1 and target1, both customer classifications, seems to work. Filtering on Date gives me problems. I tried several variations on the formula in the table without succes.

[sunny_talwar]

So for your filtered data, you expected to see 11 rows of Cust_id in your table?

[ramzi-manoubi]

Yes, that's correct.

[sunny_talwar]

May be you want this

Aggr(If(

> 0, Cust_Id), Cust_Id)

[ramzi-manoubi]

Also doens't work as soon as I start filtering on year.

Customers and Visits are connected through CustEmplDate. I want to show the customers with no visits in the year 2016. For the customer for whom there is no visit, there is also no date and thus no year. By filtering on date, all the customers without visit are already removed by the selection.

[ramzi-manoubi]

When I use this formula in the table, the filters still have effect.