Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arngue
Contributor III
Contributor III

Count nº dimension values based on the dimension values

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?

Labels (3)
1 Solution

Accepted Solutions
arngue
Contributor III
Contributor III
Author

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

View solution in original post

1 Reply
arngue
Contributor III
Contributor III
Author

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