New/Lost/Returning/Loyal Customers

[hvfalcao]

Hi!

I'm struggling to find a solution to count new/lost/returning/loyal customer over the years... I've tried different approaches posted on the community (script and expression), but couldn't reach what I want. I need it on expression! I'll neded to select years on a filter, use it on graphs...

Definitions:

New - clients that never bought, and bought on the selected year.
Lost - clients that have bought last year, but not the selected year.

Returning - clients that have bought in any year before, didn't bought the previous year, but bought the selected year.

Loyal - clients that bought previous year, and the selected year.

The previous year is always compared to the selected year, not the actual year, like today...

The data (simplified):

ORDERID

CLIENTID

YEAR

SALESQTY

IDPRODUCTCATEGORY

Thank you!

(I cant open others apps)

[hvfalcao]

Very nice and clean solution! That was exactly what I need!

Thank you!

[hectorgarcia]

This is good stuf, if you want to measure the retirning customers by Monthyear instead of year you will just need to change thus customer history tab logic to Monthname?

Thanks

[Gysbert_Wassenaar]

Basically yes. But MonthName values are dates, not sequential numbers like years. So you need to use the addmonths function instead of just adding integers.

[fluxfrog]

Thanks for this solution to a quite tricky problem! Worked great for my app too.