Discussion Board for collaboration related to QlikView App Development.
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.
[CPF / CNPJ Customer]
Max ([Date / Time]) AS ULTIMA_COMPRA,
Min ([Date / Time]) AS PRIMEIRA_COMPRA,
AddMonths (today () - 6) THE TERM
Group by [CPF / CNPJ Customer];
IF (PRIMEIRA_COMPRA> = TERM, 'NEW', 'OLD') the [Client status]
IF (ULTIMA_COMPRA> = TERM, 'ACTIVE', 'INACTIVE') the [Client Type]
DROP Table TempStatusCliente;
Try something like this:
Let vDataInicial = Num(MakeDate(2012, 1, 1));
Let vDataFinal = Num(MonthStart(Today()));
Concat(Distinct MesTemp) as MesesTemp;
Num(MonthStart($(vDataInicial) + RowNo() - 1)) as MesTemp
AutoGenerate $(vDataFinal) - $(vDataInicial) + 1;
For Each mes in Peek('MesesTemp')
MonthName($(mes)) as [Mês de Referência],
IF(PRIMEIRA_COMPRA >= PRAZO, 'NOVO', 'ANTIGO') as [Status do Cliente],
IF(ULTIMA_COMPRA >= PRAZO, 'ATIVO','INATIVO') as [Tipo de Cliente];
Max(MonthStart([Data/Hora])) AS ULTIMA_COMPRA,
Min(MonthStart([Data/Hora])) AS PRIMEIRA_COMPRA,
AddMonths($(mes),-6) AS PRAZO
Where Num(MonthStart([Data/Hora])) <= $(mes)
Group by [CPF/CNPJ Cliente];
View solution in original post
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