Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ramzi-manoubi
Contributor

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.

1 Solution

Accepted Solutions

Re: Set Analysis, non-visited customers (table)

Check the attached

26 Replies

Re: Set Analysis, non-visited customers (table)

What exactly are you filtering on?

ramzi-manoubi
Contributor

Re: Set Analysis, non-visited customers (table)

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.

Re: Set Analysis, non-visited customers (table)

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
Contributor

Re: Set Analysis, non-visited customers (table)

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.

Re: Set Analysis, non-visited customers (table)

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

ramzi-manoubi
Contributor

Re: Set Analysis, non-visited customers (table)

Yes, that's correct.

Re: Set Analysis, non-visited customers (table)

May be you want this

Aggr(If(

Capture.PNG

> 0, Cust_Id), Cust_Id)

ramzi-manoubi
Contributor

Re: Set Analysis, non-visited customers (table)

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.

visit.png

ramzi-manoubi
Contributor

Re: Set Analysis, non-visited customers (table)

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

filters not ignored.PNG

Community Browser