HOW TO CALCULATE LOST CUSTOMER

[mauvasco62]

Dear all,

i need your help in order to calculate lost customers. I know  well that it's not easy.

Lost customer for me is : customer without revenue in the last 6 months

I found an articulate script in this forum and I readjusted it with my fields.

All works fine but not for lost customer: at the moment (with this script) the lost customer is a customer with "no revenue" in the previous month but that's not what I need.

Could you please help me in order to solve this "enigma"  ?

Thanks in advance for your support

Have a nice day

Mauro

******* this is my script *******

FATTURE_TMP:

FATTURE:

Load

    ANNOMESE,  --> Yearmonth related to the revenue

    ReferenceDate,

    _KCliente,    -->  Customer code

    _KFiliale,    --> Branch code

    SUM(TOTALE_VOCE) AS TOTALE_FATT     --> Total invoice

    RESIDENT FATTURE_TMP

    GROUP By

    [_KFiliale],[_KCliente],ReferenceDate,ANNOMESE

    Order by [_KCliente],[_KFiliale],ANNOMESE;

Temp:

LOAD

num(min(FieldValue('ANNOMESE',RecNo()))) as MinAnnomese,

num(max(FieldValue('ANNOMESE',RecNo()))) as MaxAnnomese

AutoGenerate FieldValueCount('ANNOMESE')

;

LET vMinAnnomese = peek('MinAnnomese');

LET vMaxAnnomese = peek('MaxAnnomese');

Temp_Hist:

LOAD distinct

[_KCliente],

ANNOMESE as Yearmonth,

AutoNumber(ANNOMESE,[_KCliente]&[_KFiliale]) AS Index

RESIDENT FATTURE

ORDER BY [_KCliente], ANNOMESE

;

JOIN (Temp_Hist)

LOAD FieldValue('_KCliente', RecNo()) as [_KCliente], num($(vMinAnnomese) + IterNo() -1) AS Yearmonth

AutoGenerate FieldValueCount('_KCliente')

While $(vMinAnnomese) + IterNo() -1<= $(vMaxAnnomese)

;

History:

LOAD * Where rangesum(New,Lost,Returning,Loyal);

LOAD *,

if(Index = 1,1) as New,

if(CC and Previous(Index) and not alt(Index,0),1)  as Lost,

if(CC and Index>1 and not alt(Previous(Index),0),1) as Returning,

if(CC and Previous(Index)=Index-1,1) as Loyal

;

LOAD *, if(Previous([_KCliente])=[_KCliente],1,0) as CC

Resident Temp_Hist

;

Drop Table Temp;

Drop table Temp_Hist;

DROP TABLE FATTURE_TMP;

Drop Fields Index, CC;

[dan_sullivan]

Rather than mess with the data model, you might be better to just load it all in a table and use the p() and e() function to determine lost customers.

Example:

SUM({$<ANNOMESE={'$(=$(vPeriod2))'}, _KCliente=e({$<ANNOMESE={'$(=$(vPeriod1))'}>})>}TOTALE_FATT )

create a variable to define vPeriod1 (maybe the current month?) and another variable to define vPeriod2 (maybe the prior month?).

Then put customer in a table along with the measure outlined above.  It will show all customers in Period 2 that do not exist in Period 1; i.e. Lost customers

[isingh30]

Can you share your data or sample application. You want solution at script level or UI level?

[mauvasco62]

Hi Dan,

thanks for your advice. I will try ... but i prefer to manage all into the script.

I'm working fo the moment only into the "data model" leve and not in the final app.

Regards

Mauro

[mauvasco62]

Hi,

i would like a solution at script level because i'm building a data model file.

In attach you can find the file.

Thanks

Mauro