¿Cómo obtener el número de usuarios activos a partir de fecha alta y fecha baja?

[jordimtb]

Buenos días,
disculpen si el título es inexacto...
Hay la necesidad de representar "Número de usuarios activos" podría ser por año, mes...  el gráfico sería lineal.

La particularidad es que la dimensión no existe como tal, ya que por cada usuario tenemos los siguientes datos clave:
- id_usuario
- f_alta
- f_baja (puede contener una fecha o bien nulo)

Supón los siguientes registros:

id_usuario   f_alta   f_baja
usuario1   01/01/2015
usuario2    01/05/2015    31/12/2016
usuario3    01/10/2016
usuario4    01/06/2017    31/05/2018
usuario5    01/01/2018

Resultado para el gráfico

año   nºusuarios
2015    2
2016    3
2017    3
2018 4

No veo como hacerlo en QlikSense... ¿Como debería hacerlo?

¡Muchas gracias!

[jmmayoral3 · Accepted solution]

Hola Jordi.

La mejor forma es calcular en el script los periodos de actividad de cada usuario y meterlos en una tabla.

USUARIOS: //Tabla de tu modelo con datos de usuarios
LOAD id_usuario,
date(f_alta,'DD/MM/YYYY') as f_alta,
date(f_baja,'DD/MM/YYYY') as f_baja
INLINE [
id_usuario, f_alta, f_baja
usuario1, 01/01/2015
usuario2, 01/05/2015, 31/12/2016
usuario3, 01/10/2016
usuario4, 01/06/2017, 31/05/2018
usuario5, 01/01/2018
];

TMP1:  //tabla para calcular la fecha de inicio y fin de cada usuario. 
LOAD DISTINCT
id_usuario,
f_alta AS FechaInicio,
RANGEMIN(f_baja,TODAY()) AS FechaFin //Si no tiene fecha de baja, ponemos la de hoy o a la fecha que queramos.
RESIDENT USUARIOS;

// Tabla con los tramos de actividad de cada usuario
FECHAS_ACTIVIDAD_USUARIO:
LOAD
id_usuario,
Year(AddMonths(FechaInicio, IterNo() - 1)) AS AÑO_ACT_USUARIO,
Month(AddMonths(FechaInicio, IterNo() - 1)) AS MES_ACT_USUARIO,
DUAL(Month(AddMonths(FechaInicio, IterNo() - 1))&'-'&Year(AddMonths(FechaInicio, IterNo() - 1)),
Year(AddMonths(FechaInicio, IterNo() - 1)) * 100 + Month(AddMonths(FechaInicio, IterNo() - 1))) AS AÑOMES_ACT_USUARIO
Resident TMP1
While Num(Year(AddMonths(FechaInicio, IterNo() - 1)) * 100 +
Month(AddMonths(FechaInicio, IterNo() - 1))) <=
Num(Year(FechaFin) * 100 + Month(FechaFin))
;

DROP Table TMP1;

Este será el resultado final. Observa que hay algunos usuarios que, al no tener fecha de baja, se le ha asignado la fecha de hoy (28/01/2019)

Espero que te sirva.

Saludos.

[jordimtb]

Pido disculpas por la tardanza, no he podido testearlo hasta ahora.
Bien, me devuelve el resultado exacto!!! Per-fec-to.

Pero no logro entender qué hace en el script para generar la tabla "FECHAS_ACTIVIDAD_USUARIO". ¿Me podría hacer una "traducción" de la acción?

¡Muchísimas gracias!

[jmmayoral3]

Hola Jordi.

Me alegro de que te sirva.  Recuerda marcar la respuesta como correcta.

Te explico el script a continuación:

Lo primero que hacemos es crear una tabla con el rango de fechas que el usuario ha estado de alta. Tomamos las fechas de inicio (alta)  y fin (baja) para cada usuario. Si no tiene fecha de baja, entonces tomamos la fecha hasta hoy (Tabla TMP1).

Imagina que el usuario A fue alta el 13/1/2019 y baja el 24/3/2019.

La tabla TMP1 tiene: 
usuario= A
FechaInicio=13/1/2019
FechaFin=24/3/2019

En la tabla FECHAS_ACTIVIDAD_USUARIO lo que hacemos es calcular para cada usuario cada una de las fechas (días) de los intervalos calculados anteriormente en la tabla TMP1.
Fíjate que el bucle se repite mientras la fecha inicio (convertida a formato numérico YYYYMM) sea menor que la fecha de fin en el mismo formato.
Los campos que genera esa tabla es el AÑO_ACT_USUARIO (año de actividad en formato YYYY), MES_ACT_USUARIO (Mes formato MM) y AÑO_MES_ACT_USUARIO.
Este último campo tiene el formato numérico YYYYMM para hacer cálculos y ordenaciones cuando se usa en gráficos, pero además, al ser dual, también tiene el formato TEXTO  (ej: 03-2019 ) para poder mostrar algo legible en gráficos.

Cambiando esa primera línea de la función dual() podrías tener la fecha en formato 'mar-2019'.

siguiendo nuestro ejemplo, el usuario A tendría estos registros en la tabla FECHAS_ACTIVIDAD_USUARIO:

id_usuario            AÑO_ACT_USUARIO         MES_ACT_USUARIO            AÑO_MES_ACT_USUARIO
A                                   2019                                                    1                                  1-2019  (texto)   y  201901 (num)   
A                                   2019                                                    2                                  2-2019  (texto)   y  201902 (num)   
A                                   2019                                                    3                                  3-2019  (texto)   y  201903 (num)   

Date cuenta de que sólo te dice qué periodo mes-año está de alta. Si necesitases ir a nivel de día, tendrías que incluir el día en las fórmulas del While y de los campos de esta tabla.

Saludos.          

[JonyBGood]

Hola buenas tardes! No logro entender como llevarlo a nivel de granularidad de fecha. Cómo debería hacerlo?

Gracias!

[JonyBGood]

Hola buenas tardes! No logro entender como llevar a un nivel de granularidad de fecha. ¿Cómo debería hacerlo?

¡Gracias!

[JonyBGood]

Hola buenas tardes! No logro entender como llevar a un nivel de granularidad de fecha. ¿Cómo debería hacerlo?

¡Gracias!

[jmmayoral3]

Si lo que buscas es una granularidad a nivel de día, tendrías que sustituir el trozo de script que crea la tabla con fechas de actividad por esto: (pruébalo y me cuentas)

FECHAS_ACTIVIDAD_USUARIO:
LOAD
id_usuario,
Year(AddMonths(FechaInicio, IterNo() - 1)) AS AÑO_ACT_USUARIO,
Month(AddMonths(FechaInicio, IterNo() - 1)) AS MES_ACT_USUARIO,
Day(AddMonths(FechaInicio, IterNo() - 1)) AS DIA_ACT_USUARIO,
DUAL(Day(AddMonths(FechaInicio, IterNo() - 1))&'-'&Month(AddMonths(FechaInicio, IterNo() - 1))&'-'&Year(AddMonths(FechaInicio, IterNo() - 1)),
Year(AddMonths(FechaInicio, IterNo() - 1)) * 10000 + Month(AddMonths(FechaInicio, IterNo() - 1))*100 + Day(AddMonths(FechaInicio, IterNo() - 1))) AS AÑOMESDIA_ACT_USUARIO
Resident TMP1
While Num(Year(AddMonths(FechaInicio, IterNo() - 1)) * 10000 + Month(AddMonths(FechaInicio, IterNo() - 1))*100 + Day(AddMonths(FechaInicio, IterNo() - 1))) <=
Num(Year(FechaFin) * 10000 + Month(FechaFin)*100 + Day(FechaFin))
;

[JonyBGood]

Excelente! Muchas gracias!

[JonyBGood]

Buenas tardes! El algoritmo funciona perfecto. Me trae los usuarios activos dentro de un AñoMes, pero tengo diferencias cuando lo quiero aplicar a un grupo de usuarios que tuvieron categorias A y B en el mismo mes y solo me toma una categoria de usuario activo. Lo que quiero lograr, en ese mes, que me lo cuente en ambas categorias como activo. No se como lograrlo. Alguna idea para orientarme?

Gracias!