Identify the New customer based on Logic

[alis2063]

Hi Guys,

I am looking for very good dynamic approach to fulfill the requirement 

I have a list of customers  including the invoice date so basically i want to build the KPI  for New Customer

and New Customer is the customer who has purchase something recently and second latest transaction would be happened  more the 60 days earlier then it would be considered as  'New Customer'

Below is Customers with Invoice Date,

Customer	Date	Customer Type
A	22-01-2018	New
A	01-01-2019	New
A	05-01-2019	New
A	11-01-2019	New
A	21-07-2019	New
B	02-01-2019	Existing
B	21-01-2019	Existing
B	06-02-2019	Existing
C	27-12-2018	Existing
C	02-01-2019	Existing
C	05-01-2019	Existing
C	05-03-2019	Existing

Something like that

For Customer A-

Max Date is 21-07-2019

Second  Latest Transaction is 11-01-2019

The diff of above date is >60

It should be dynamic  if i select month the further calculation can be done on the fly .

Regards,

Ali

[alis2063]

@sunny_talwar  

please help on above issues.

[tresesco]

Do you want this in a chart? Could you share a sample app?

[alis2063]

it will be reflect in KPI box  as below,

[tresesco · Accepted solution]

Try expression like:

=Count(DISTINCT {<Customer={"=Max(Date)-Max(Date,2)>60"}>}Customer)

[alis2063]

Thanks for your solution  Tresesco.....

[alis2063]

How it  does work using the below expression  as you took the customer in set analysis 

Customer={"=Max(Date)-Max(Date,2)>60"}

[tresesco]

It's like advanced search in listbox/on field. For every customer the condition is checked and counted the customers where the condition comes true. Hope this helps.

For better understanding learn set analysis in depth.

Please don't forget to close the thread by giving 'Like' and accepting solution.

[alis2063]

Sure Tresesco ,

If  i select any date  from date filter it doesn't work any more......

[tresesco]

You are probably using extension objects I don't have, so I can't check it on your app. Could you explain your selection and explain expected output here?