Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramzi-manoubi
Partner - Creator
Partner - Creator

Set Analysis, non-visited customers (table)

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.

26 Replies
ramzi-manoubi
Partner - Creator
Partner - Creator
Author

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.

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

It has been sometime and I am not completely sure what you want, but check the attached

Capture.PNG

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

Check the attached

ramzi-manoubi
Partner - Creator
Partner - Creator
Author

Thank you very much.

You've been an incredible help to me.

sunny_talwar

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