Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mauvasco62
New Contributor II

HOW TO CALCULATE LOST CUSTOMER

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;

4 Replies
dan_sullivan
Contributor II

Re: HOW TO CALCULATE LOST CUSTOMER

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
Contributor III

Re: HOW TO CALCULATE LOST CUSTOMER

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

mauvasco62
New Contributor II

Re: HOW TO CALCULATE LOST CUSTOMER

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
New Contributor II

Re: HOW TO CALCULATE LOST CUSTOMER

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