Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to count how many PK were active in a date.
I have a data table with the year that a customer withdraw.
I need to count how many customers were active on a year.
I need to represent this as a Line Chart. Dimension is Year, Expression is count
Something like:
count(distinct PK) + count(distinct <{Year>Year}> PK) + count(distinct <{Year=null()}> PK)
Count(distinct PK) - counts how many customers withdrawn in the dimension year
Count(distinct <{Year > Year}> PK) - counts how many customers withdrawn in future of the dimension year
Count(distinct <{Year = null()}> PK) - counts how many customers never been withdrawn.
Any way to acomplish that? I'm getting insane.
Do i have to accomplish that in the Load script?
This is what I've done, hope it helps someone:
MinFechaBaja:
LOAD
Min(Cl_FechaDesdeYear) as MinimaFecha
Resident CRM_Cuenta;
let vFechaMin = Peek('MinimaFecha',-1,'MinimaFecha');
let vFechaMax = year(Today());
FOR vFechaActual = $(vFechaMin) to $(vFechaMax)
if $(vFechaActual) = $(vFechaMin) then
CRM_HistorialCuentasActivas:
Load
Cl_NombreCliente as HCA_Cliente,
$(vFechaActual) as HCA_Año,
1 as HCA_Contador
Resident CRM_Cuenta
where (Cl_FechaDesdeYear<=$(vFechaActual) and Cl_FechaHastaYear>=$(vFechaActual)) or (Cl_FechaDesdeYear<=$(vFechaActual) and isnull(Cl_FechaHasta_tmp));
else
Concatenate (CRM_HistorialCuentasActivas)
Load
Cl_NombreCliente as HCA_Cliente,
$(vFechaActual) as HCA_Año,
1 as HCA_Contador
Resident CRM_Cuenta
where (Cl_FechaDesdeYear<=$(vFechaActual) and Cl_FechaHastaYear>=$(vFechaActual)) or (Cl_FechaDesdeYear<=$(vFechaActual) and isnull(Cl_FechaHasta_tmp));
end if
next
This is what I've done, hope it helps someone:
MinFechaBaja:
LOAD
Min(Cl_FechaDesdeYear) as MinimaFecha
Resident CRM_Cuenta;
let vFechaMin = Peek('MinimaFecha',-1,'MinimaFecha');
let vFechaMax = year(Today());
FOR vFechaActual = $(vFechaMin) to $(vFechaMax)
if $(vFechaActual) = $(vFechaMin) then
CRM_HistorialCuentasActivas:
Load
Cl_NombreCliente as HCA_Cliente,
$(vFechaActual) as HCA_Año,
1 as HCA_Contador
Resident CRM_Cuenta
where (Cl_FechaDesdeYear<=$(vFechaActual) and Cl_FechaHastaYear>=$(vFechaActual)) or (Cl_FechaDesdeYear<=$(vFechaActual) and isnull(Cl_FechaHasta_tmp));
else
Concatenate (CRM_HistorialCuentasActivas)
Load
Cl_NombreCliente as HCA_Cliente,
$(vFechaActual) as HCA_Año,
1 as HCA_Contador
Resident CRM_Cuenta
where (Cl_FechaDesdeYear<=$(vFechaActual) and Cl_FechaHastaYear>=$(vFechaActual)) or (Cl_FechaDesdeYear<=$(vFechaActual) and isnull(Cl_FechaHasta_tmp));
end if
next