Bridge Analysis : New, Lost, Loyal customer - Qlik Community

Report Inappropriate Content · ‎2012-07-15

Hello

In the Gap betwenn 2012 and 2011 turnvover, i would like to know the turnover from new customer, Loyal customer, and lost customer.They are defined as followed:

New customer: Customer That has no turnover in the Similar month in the PY but has in the actual one

Loyal customer: Customer that has turnover in both period

Lost customer: Customer that has turnover in the Similar month in the PY but not in the actual one

of course this is moving depending on the month selection

can you help me using the enclosed exemple?

swuehl · ‎2012-07-15

I think you can use expressions using set modifiers with implicite field value definitions (namely e() and p() functions) for this, applied to the Client field like

=sum( {$<Année = {$(#=Only(Année))},Client *= p({<Année ={$(#=Only(Année)-1)} >}) >} [Sales])

=sum( {$<Année = {$(#=Only(Année))},Client *= e({<Année ={$(#=Only(Année)-1)} >}) >} [Sales])

=sum( {$<Année = {$(#=Only(Année)-1)},Client *= e({<Année ={$(#=Only(Année))} >}) >} [Sales])

to get Sales for Customers connected with both years, only current year and only past year.