Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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?

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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

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
Highlighted
Contributor III
Contributor III

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

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