Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

History of the number of active clients

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;

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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;

View solution in original post

2 Replies
Highlighted

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

Highlighted
Creator III
Creator III

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;

View solution in original post