4 Replies Latest reply: Jun 19, 2017 10:28 AM by Simone Trabattoni RSS

    e() Function and problem with filters

    Simone Trabattoni

      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:

      e()1.PNG

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

       

      e()2.PNG

      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:

       

      e()3.PNG

      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:

       

      e()4.PNG

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

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