Quarterwise Addition of New Customers logic

[sunainapawar]

Hi All,

I have a requirement of finding New Customers added Quarter Wise.

I have data from 2019 till present. FYear is considered from Apr-till March.

Suppose, if i select Current Financial Year as 2020/2021, then i need to check if the customer_ids  from Apr 2019-till present August 2020, i.e.,the Quarters Q1,Q2,Q3 and Q4 of 2019 and the present Quarter of 2020 i.e,Q1 and Q2, is there any transaction done by the customer_ids. If there is no transaction done in all this Quarters, like if it is equal to 0 , then that customer_id will be considered as "New Customer". If Transaction is made in any of the Quarter and customer_id already exists, then it must be considered as "Existing Customer". 

I tried to create a flag in the script with below logic,

Transaction_table:

Load 

customer_id,

amount,

date

from trans_table;

temp:

load *,

if(customer_id=previous(customer_id),'Existing Customer','New Customer') as Flag

resident Transaction_table order by customer_id,date;

drop table Transaction_table ;

But how can i show this in front End in the chart Quarter Wise.

Please Suggest.

Thanks in advance

[sunainapawar]

Hi All,

This is basically finding the duplicates in Customer_id. I tried below, but when we select New Customer, it is also including the customer_id those are existing. In my case, the New Customer must not include the Existing Customer_ids.

In below example, id 2 is repeated. So 2 must be existing customer, but when new customer flag is selected, this id 2 must be excluded. But this is included in New Customers. I have attached the screenshot for reference. 

Table:

LOAD * INLINE [

id, date, phone

1, 1/1/2017, a

2, 2/1/2017, d

3, 4/1/2017, a

4, 5/1/2017, a

2, 1/1/2017, d

6,12/1/2017, e

];

FinalTable:

LOAD *,

If(id = Previous(id), 'Existing customer', 'New Customer') as Flag

Resident Table

Order By id, date;

DROP Table Table;

Any help would be appreciated. Thanks in advance.