Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following tables
Cust_id | Transaction_id | Amount | Date |
---|---|---|---|
1 | 001 | 1000 | 01/01/2016 |
2 | 002 | 231 | 11/01/2016 |
3 | 003 | 2342 | 02/01/2016 |
4 | 004 | 1100 | 20/01/2016 |
Cust_id | Cust_Name |
---|---|
1 | gaurav |
2 | Anil |
3 | Jack |
4 | David |
So the above two tables are linked with Cust_id
and these tables have data for 1 year
And the logic for Repeat and New Customer is,
Customer how has more than 1 transaction is the Repeat Customer (Transaction_id >1).
Customer who has only 1 transaction is a new customer (Transaction_id=1)
On the selected date i want the number of New Customer and Number of Repeat Customers
I also want which are the Customer Name in a table which are new and Repeat Customers
Basically i want count of New and Repeat Customers in KPI
and their details in the Report
Can anyone help me ?
Can we do this by Aggr() function ?
Thanks in Advance
May be something like this:
Repeat Customer
Count(DISTINCT {<Customer = {"=Count({<Date = {'<=$(=Date(Max(Date)))'}>}Transaction_id) > 1"}>} Customer)
New Customer
Count(DISTINCT {<Customer = {"=Count({<Date = {'<=$(=Date(Max(Date)))'}>}Transaction_id) = 1"}>} Customer)