How to count retourning Customer

[AN2024]

I need to count the retourning customer:

A retourning customer is defined as:

Buy a product in the latest day of the Data base.

- They didn´t buy a product in the last 6 months

- They bought a product from 7 months to the beginning of the Data base.

I have this code but it doesn´t correct correctly:

count({<
Customer=e({<Transaction_date={'>=$(=Date(AddMonths(Max(Transaction_date), -6), 'DD/MM/YYYY'))<=$(=Date(Max(Transaction_date)-1, 'DD/MM/YYYY'))'}, Customer-={'*'}>}),
Transaction_date={"=Sum(Sales) > 0"},
Customer-={"=Sum({<Transaction_date={'>=$(=Date(AddMonths(Max(Transaction_date), -6), 'DD/MM/YYYY'))<=$(=Date(Min(Transaction_date)-1, 'DD/MM/YYYY'))'}>} TVLMSLD) = 0"}
>} DISTINCT Customer)

Thank you in advances

[marcus_sommer]

IMO it's not possible with a set analysis because they worked like a selection and is not a row-level evaluation.

You may switch your logic into n if-loops and depending on the view-requirement you will quite likely also need some aggr() wrapping to it - to ensure that each evaluation happens against a single customer.

I doubt that I would go in this direction else I would try to create the essential information within the data-model. This might be to flag the first and last buy + creating a running number + accumulated amounts on the bought. Further an offset-value on the period-differences from the last buy to the previous ones and also one against today(). And quite likely on top of it various kinds aggregations to counts and amounts as well as of flagging, clustering and scoring the all these information.

The aggregations need group by loads and for the accumulation you could use interrecord-functions like peek() and previous() within sorted resident loads.