History of the number of active clients

[#1]

Expensive,

I have the following situation: Today I classify my clients:

INACTIVE customers and ASSETS customers

and customers NEW and OLD customers

ASSETS customers are customers who have their ULTIMA purchase within the last 6 months, unlike are INACTIVE (Customer Type)

NEW customers are customers who have your FIRST purchase in the last six months, unlike are OLD (Customer Status)

My code now works perfectly, but he does not keep the history of the period prior to the last six months of Today ().

Have a time line graph which are presented in the dimension Month/year the count of these Customers ratings.

I wonder how to save this historic amount of Customers of these classifications.

Code:

TempStatusCliente:

LOAD

[CPF / CNPJ Customer]

Max ([Date / Time]) AS ULTIMA_COMPRA,

Min ([Date / Time]) AS PRIMEIRA_COMPRA,

AddMonths (today () - 6) THE TERM

Resident Apparel

Group by [CPF / CNPJ Customer];

[StatusCliente]:

LOAD

[CPF / CNPJ Customer]

IF (PRIMEIRA_COMPRA> = TERM, 'NEW', 'OLD') the [Client status]

IF (ULTIMA_COMPRA> = TERM, 'ACTIVE', 'INACTIVE') the [Client Type]

Resident TempStatusCliente;

DROP Table TempStatusCliente;

[MarcoWedel]

Hi,

you could add a field

today() as ReportDate

and store the StatusCliente table to a qvd that holds the complete history of this table.

You then could use the ReportDate as the dimension for historical analysis of the client status.

hope this helps

regards

Marco

[Anonymous · Accepted solution]

Hello, Bruna.

Try something like this:

Let vDataInicial = Num(MakeDate(2012, 1, 1));

Let vDataFinal = Num(MonthStart(Today()));

MesesTemp:

Load

  Concat(Distinct MesTemp) as MesesTemp;

Load Distinct

  Num(MonthStart($(vDataInicial) + RowNo() - 1)) as MesTemp

AutoGenerate $(vDataFinal) - $(vDataInicial) + 1;

For Each mes in Peek('MesesTemp')

[StatusCliente]:

LOAD

  MonthName($(mes)) as [Mês de Referência],

  [CPF/CNPJ Cliente],

  IF(PRIMEIRA_COMPRA >= PRAZO, 'NOVO', 'ANTIGO') as [Status do Cliente],

  IF(ULTIMA_COMPRA  >= PRAZO, 'ATIVO','INATIVO') as [Tipo de Cliente];

LOAD

  [CPF/CNPJ Cliente],

  Max(MonthStart([Data/Hora])) AS ULTIMA_COMPRA,

  Min(MonthStart([Data/Hora])) AS PRIMEIRA_COMPRA,

  AddMonths($(mes),-6) AS PRAZO

Resident Fato

Where Num(MonthStart([Data/Hora])) <= $(mes)

Group by [CPF/CNPJ Cliente];

Next mes;