topic Re: Set Analysis, non-visited customers (table) in App Development

Set Analysis, non-visited customers (table)

ramzi-manoubi — Mon, 06 Mar 2017 15:38:21 GMT

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.

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

sunny_talwar — Mon, 06 Mar 2017 16:02:36 GMT

What exactly are you filtering on?

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

ramzi-manoubi — Mon, 06 Mar 2017 17:46:41 GMT

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)

sunny_talwar — Mon, 06 Mar 2017 19:28:11 GMT

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?

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

ramzi-manoubi — Wed, 08 Mar 2017 10:20:13 GMT

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)

sunny_talwar — Wed, 08 Mar 2017 10:28:17 GMT

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

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

ramzi-manoubi — Wed, 08 Mar 2017 10:51:10 GMT

Yes, that's correct.

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

sunny_talwar — Wed, 08 Mar 2017 11:55:37 GMT

May be you want this

Aggr(If(

> 0, Cust_Id), Cust_Id)

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

ramzi-manoubi — Wed, 08 Mar 2017 12:30:04 GMT

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.

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

ramzi-manoubi — Wed, 08 Mar 2017 12:49:39 GMT

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

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

sunny_talwar — Wed, 08 Mar 2017 12:51:18 GMT

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

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

ramzi-manoubi — Wed, 08 Mar 2017 12:58:37 GMT

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

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

sunny_talwar — Wed, 08 Mar 2017 13:13:35 GMT

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

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

ramzi-manoubi — Wed, 08 Mar 2017 13:17:07 GMT

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.

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

ramzi-manoubi — Wed, 08 Mar 2017 15:18:20 GMT

With this formula, why do filters still have effect?

Derived Dimension:

=aggr(

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

Cust_Id

Cust_Id)

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

sunny_talwar — Wed, 08 Mar 2017 15:19:13 GMT

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

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

ramzi-manoubi — Wed, 08 Mar 2017 15:56:30 GMT

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.

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

karthiksrqv — Thu, 09 Mar 2017 12:43:15 GMT

Hi,

Do you think this will work?

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

along with a

=sum({1} <metric field>)

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

ramzi-manoubi — Thu, 09 Mar 2017 12:48:31 GMT

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.

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

karthiksrqv — Thu, 09 Mar 2017 12:57:07 GMT

Sure.

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