Count nº dimension values based on the dimension values

arngue — Sat, 16 Nov 2024 04:06:52 GMT

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?

Re: Count nº dimension values based on the dimension values

arngue — Wed, 20 Mar 2019 09:48:41 GMT

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

topic Re: Count nº dimension values based on the dimension values in QlikView

Count nº dimension values based on the dimension values

Re: Count nº dimension values based on the dimension values