Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Tresesco,
I have attached the doc with output explained in detail,
Also i have removed the extension and included the in built KPI
Hi, I am not @tresesco , but you can try this
Count(DISTINCT {<Customer={"=Max(Date)-Max({<Date = {[<$(=Max(Date))]}>}Date)>60"}>}Customer)
Basically adding another set analysis for the second max date which is saying that include all dates which are less than the max date and find there max.