returning customer count

[aatish12345]

Dear All,

I have date and customerID these 2 fields from same table ,I want to compare the customer ID from 2019 year with CustomerID from 2020 year , since date field has only 2 years 

I want to compare customerID From 2019 with customerID From  2020 AND Want to get count where it got matched with 2020, so that i would be able to find returning customer.

I am not getting a way to do this

Kindly help!

Regards,

Aatish

[shwethaa]

Hi Aatish,

If you want to count number of Customer are lie in both 2019 and 2020 then you can try below expression- 

Count({<Year={"2019"},CUSTOMER_ID=p({<Year={"2020"}>}CUSTOMER_ID)>}Distinct CUSTOMER_ID)

Let me know if this works.

[brunobertels]

Hi 

May be this 

=count({< CustomerID= p({<[Date]={$(=max(year(Date))-1)}>}  CustomerID) >} CustomerID)

[aatish12345]

Yes, it is working fine, now I wanted to extract new customers out of it, so I have taken a difference between customers of 2020 and returning customers in 2020, but it is not giving expected result in chart, If I display new customer count in KPI then it is giving correct number. My requirement is to show both returning and new customer in bar chart.

Here in new customer --> it is calculating total customers in 2020 and showing negative bar for returning customer 

Here original new customer count is 11067, but it is showing 12713 and remaining value in term of negative bar

Plz help if possible

[brunobertels]

Hi 

We used P function to count for a Year the customer who were there the year before like this : 

The P function INCLUDE 

=count({< CustomerID= p({<[Date]={$(=max(year(Date))-1)}>}  CustomerID) >} CustomerID)

So may be you can try the E function who EXCLUDE values to get only NEW customer 

=count({< CustomerID= E({<[Date]={$(=max(year(Date))-1)}>}  CustomerID) >} CustomerID)

nethertheless i 'm pretty not sure as set analysis is'nt my "native language" 

[aatish12345]

Thanks for the reply dear brunobertels,

I will try E function  for sure and get back to you