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. 

[sunny_talwar]

It was my bad, try this (forgot to update the inner "" to [])..

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]

Hi Sunny. I know its difficult working with and expression only. I attach a very set of sample data for 2 Customers. The result for the exp should be 1 for customer code ZEL001. Hopefully this can give some insight. 

[sunny_talwar · Accepted solution]

This worked for me

Count(DISTINCT {<
[Customer Code] = {"=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 and Sum({$<[Year], Month, [_BaseMonth] = {[<=$(=Year(Today())*12 + Month(Today())) >= $(=Year(Today())*12 + Month(Today())-12)]}>} Sales) > 0"}
>} [Customer Code])

[johngouws]

Sunny - That's it. I recopied the expression. Maybe I missed something. 

The attached file returns the correct result. 

Thank you very much for the assistance - again.

[samp]

Hi Sunny, quick question on below expression based on your valid formula what it means exactly:

Which field will try to compare between, since we gave 3 different fields.

Sum({$<[Year], Month, [_BaseMonth] = {[<=24225 >=24214]}>} Sales)

[johngouws]

Hi Samp. If I may give some background on the original thought. Using your code: Now is = _BaseMonth 24226

What I needed to achieve is count the Customers who purchased something in [_BaseMonth] = {[<=24202 >=24191]}, purchased ZERO in [_BaseMonth] = {[<=24214 >=24203] and then started purchasing again in [_BaseMonth] = {[<=24226>=24215]}.

Find Customers where sum of Sales > 0 for these 12 months;
Sum({$<[Year], Month, [_BaseMonth] = {[<=24226>=24215]}>} Sales) //Need Sales > 0

Find Customers where sum of Sales = 0 for these 12 months;
Sum({$<[Year], Month, [_BaseMonth] = {[<=24214 >=24203]}>} Sales) //Need Sales = 0

Find Customers where sum of Sales > 0 for these 12 months;
Sum({$<[Year], Month, [_BaseMonth] = {[<=24202 >=24191]}>} Sales) //Need Sales > 0

To get the final resulst you need the 3 expressions in one.

Hopefully this can assist. 

[samp]

Thanks John for your explanation, I am trying to understand why to add [Year], Month in the expression though we already comparing with the filed [_BaseMonth].

Sum({$<[Year], Month, [_BaseMonth] = {[<=24226>=24215]}>} Sales)

[sunny_talwar]

Set analysis conditions are intersections.. so basically if you don't ignore selection in year or month.... you will end up seeing the selected year. So, although you request a range based on _BaseMonth, your year selection will intersect with it and give you only the partial or no output based on the intersection of the two sets.

[samp]

Thanks Sunny, is there any help or Blog to understand more?

[sunny_talwar]

Not sure what your level of understanding is... but you can start with a very basic here A Primer on Set Analysis