Count returning Customers with set analysis

[johngouws]

Good day All.

I have found a few similar posts on the Community but I cannot seem to adapt any to what I need to achieve. I am sure this must be achievable, some how. 

I am trying to calculate the number of Customers who used to purchase form us, left for a year and has started purchasing again.
The logic would be,
Count Customers who purchased historically 25 to 36 months ago,
had ZERO purchases 13 to 24 months ago
but do have purchases in the last 12 months.

For simplicity, in year terms, it would be.
Sales in 2017
ZERO Sales in 2018
Sales in 2019.

Where I am at. I have 3 expressions for calculating each of the individual calculations, but am unable to bring it together in a single expression. Please could you assist in making this possible.

//Returns 920 Customers who had Sales in the past 12 months.
//Count({$<[Year]=,Month=,[_BaseMonth]={"<=$(=Year(Today())*12 + Month(Today()) ) >= $(=Year(Today())*12 + Month(Today())-12)"}, [Customer Code] = {"=Sum(Sales)>0"}>} Distinct [Customer Code])

//Returns 277 Customers who had ZERO Sales in the past 13 to 24 months.
//Count({$<[Year]=,Month=,[_BaseMonth]={"<=$(=Year(Today())*12 + Month(Today())-13) >= $(=Year(Today())*12 + Month(Today())-24)"}, [Customer Code] = {"=Sum(Sales)=0"}>} Distinct [Customer Code])

//Returns 743 Customers who had Sales in the past 25 to 36 months.
//Count({$<[Year]=,Month=,[_BaseMonth]={"<=$(=Year(Today())*12 + Month(Today())-25) >= $(=Year(Today())*12 + Month(Today())-36)"}, [Customer Code] = {"=Sum(Sales)>0"}>} Distinct [Customer Code])

Info: The field "_BaseMonth" is just a numeric number for each month created in the script from "Year(Today())*12 + Month(Today())"

Thank you in advance for the assistance. 

[Rohan]

Hello,

I think you should take a resident of your main data & use the Order by clause to get the data into ascending order with respect to [Customer Code] & [Order Date] & then create a flag in them to separate the Historical Customers who are coming back & those who haven't. This can be achieved by :

//Always Remember to Put NoConcatenate 

New :

Load

*,

if(

    [Customer Code] =Above([Customer Code]),

          if(

                Previous(Year(Order_Date))-Year(Today())>2 and Year(Order_Date)-Year(Today())=0,

                 'Returning Customer',

                  null()

             )

) as Flag

Resident Main_Table order by Customer_Code, Order_Date asc ;

After this you can Drop Your Main Table;

Now You can simply pull this Flag in the Set expression like :

Count({<Flag={"Returning Customer"}>} distinct Customer_Code) 

The above Set Expression should be able to give you the desired Count of Returning Customers

Let me know if this worked or if you have any Queries regarding this.

Thanks & Regards 

Rohan S. Desai

[johngouws]

Hi Rohan. 

Thank you for your reply - That looks like it may work. I am getting a syntax error. Checked the bracketing and it all seems good. 

Can you spot anything I am doing wrong? 

NoConcatenate

t2:
Load
*,
if(
[Customer Code] = Above([Customer Code]),
if(
Previous(Year(Date))-Year(Today())>2 and Year(Date)-Year(Today())=0,
'Returning Customer',
null()
)
) as Flag

Resident t1 order by [Customer Code], Date asc ;
Drop Table t1;

[Rohan]

Hello,

Write Previous instead of Above because Above is not a valid Script Function.

Thanks & Regards.

Rohan S. Desai

[sunny_talwar]

Can we just try this

Count(DISTINCT {<

[Customer Code] = {"=Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())) >=$(=Year(Today())*12 + Month(Today())-12)"}>} Sales) > 0 and Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-13) >= $(=Year(Today())*12 + Month(Today())-24)"}>} Sales) = 0 and Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-25) >= $(=Year(Today())*12 + Month(Today())-36)"}>} Sales) > 0"}

>} [Customer Code])

[johngouws]

Thank you @sunny_talwar  and @Rohan . It looks like both solutions returns the same value - 0. I would like to do some testing to validate that the number is truly zero. I am surprised. As soon as I have done the testing I will reply again. 

Thank you very much.  

[sunny_talwar]

One way to check this in the dashboard is to create a new straight table like this

Dimension

Customer Code

Expressions

=Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())) >=$(=Year(Today())*12 + Month(Today())-12)"}>} Sales) > 0

=Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-13) >= $(=Year(Today())*12 + Month(Today())-24)"}>} Sales) = 0

Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-25) >= $(=Year(Today())*12 + Month(Today())-36)"}>} Sales) > 0

Do you see any row where a customer code have all three expressions with the value -1? or do you see one of the three columns to be 0 for all Customer Code?

[johngouws]

Hi Sunny. 

When I run the expressions in a straight table, showing -1 and export the table the result is 35 Customers. 

[sunny_talwar]

And this doesn't give you 35?

Count(DISTINCT {<[Customer Code] = {"=Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())) >=$(=Year(Today())*12 + Month(Today())-12)"}>} Sales) > 0 and Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-13) >= $(=Year(Today())*12 + Month(Today())-24)"}>} Sales) = 0 and Sum({$<[Year], Month, [_BaseMonth] = {"<=$(=Year(Today())*12 + Month(Today())-25) >= $(=Year(Today())*12 + Month(Today())-36)"}>} Sales) > 0"}>} [Customer Code])

[johngouws]

I hate to say it, but no. This is the same straight table with the expression added and the same filters applied. I made no changes to the last exp you sent. Very strange.