e() Function and problem with filters

[simotrab]

Hi community,

I'm working on an app that implies the use of the e() function: bibliography is quite vaste, here an example:

P() & E()

However, my problem is not using those nice functions, but their interactions with some filters.

Here an explanation. I got those data:

load*Inline

[Customer.Key,Agent,Val.Order,Period

aa,a,1,N-1

ab,a,2,N-1

ac,a,3,CUR

ab,a,2,CUR

ba,b,3,N-1

];

And I want to count, for each agent (Agent), the number of customer (Customer.Key) that are considered dead (i.e. customers that have done an order in the period N-1-previous period-, but not in the CUR -current- period-) (problem 1), and know exactly who they are (problem 2).

(problem 1)

As you can easily see, in this case the answer (in a table) should be:

a 1

b 1

to do this, I've used the e() function, in this formula:

Count(DISTINCT {<Period={"N-1"},Customer.Key= e({<Period={"CUR"}>}Customer.Key)>} Customer.Key)

I.E. count all the customers that are present in the period N-1 but are not present in the CUR period.

I've put the DISTINCT because I am interested in the nominal cardinality of the customer. The result is pretty nice:

The problem is that if I select one of the Customer, the result is this:

And it happens for each of the customer (except the ab, the only one present in the two period, N-1 and CUR).

I do not understand why this happens,I've thought that the numbers of the formula stay:

1 (in this case, generally one plus one plus...as the number of selected dead customer) if I choose one client counted in the formula.

0 (in this case) if I choose one client not counted, or a the costant previous number.

I know that I could make the formula not sensible to the filter, but I think it is not the right way, also for the next point.

(problem 2)

Furthermore when you count some customers that are dead, you'd like to know who they are: how do you deal this?

I translated the filter of the customers into a table: in this way, putting the previous formula:

I know that is not too much elegant and I know that a mix of aggr() and set analysis could do something more pretty, but using the previous formula automatically choose the dead customer. The real problem is that this become "crazy" selecting something like in the (problem1), here we go:

Luckily the same customers of the (problem1) are the problem.

Could you please help me? Thanks in advance and I hope to be clear.

[sunny_talwar]

For 1, may be try this

Count(DISTINCT {<Period={"N-1"},Customer.Key= e({<Period={"CUR"}, Customer.Key>}Customer.Key)>} Customer.Key)

Update: May be try the same for both of them....

or this

Count(DISTINCT {<Period={"N-1"},Customer.Key= e({1<Period={"CUR"}>}Customer.Key)>} Customer.Key)

[simotrab]

Hi Sunny,

it works till now, thanks: may I ask you the logic behind the first formula? It is interesting but I do not understand perfectly it (you gave me the fish, now I want to learn to fish!). Which is the logical difference with mine?

[sunny_talwar · Accepted solution]

‌The problem is that as soon as you select a single customer, your whole app drill down to that single customer. That is true for your e() inner set analysis where I just told it to ignore selection in your customer field.

does it make sense now?

[simotrab]

Yes, thanks a lot.