► Cálculo de facturas vencidas

Es muy común tener que hacer listados en las que se muestren facturas vencidas a partir de unos rangos de vencimiento. Por ejemplo de 0-1 meses, 1 a 2 meses, 2 a 3 meses, 3 a 6 meses.... etc.

Y he visto todo tipo de soluciones para resolver el problema, desde crear expresiones con complejos Set Analysis hasta tablas de conversión de periodos de vencimiento pasando por complejos if anidados tanto en script como en expresiones.

Esta solución es una más y la que yo suelo usar.

Consiste en añadir un campo más con el periodo de vencimiento a cada registro de factura. Este periodo se calcula en el script y se actualiza cada vez que se recarga la aplicación, lo que mejora luego bastante su rendimiento porque no hará falta ningún set analysis ni ifs anidados en las expresiones.

Para no complicar mucho el tema, imaginemos que tenemos esta tabla de facturas:

FACTURAS:
LOAD * INLINE [
Num_factura, FechaFactura, FechaVencimiento
1 ,          13/02/2018,   13/03/2018
2,           13/02/2018,   25/08/2018
3,           25/07/2018,   25/11/2018
]; 

Y las queremos clasificar en los siguientes rangos de vencimiento de modo que el usuario seleccione uno de ellos y automáticamente nos aparezca un listado con las facturas vencidas de ese periodo.

0 a 1 meses, 1 a 2 meses, 2 a 3 meses, 3 a 6 meses, 6 a 12 meses. 12 a 24 meses, 24 a 36 meses y más de 36 meses.

Siguiendo la regla de oro de que todo lo que se pueda calcular en el script hay que hacerlo en el script mejor que en expresiones de objetos, calculamos el rango de vencimiento y lo añadimos como un nuevo campo.

LEFT JOIN(FACTURAS)
LOAD *,
Pick(ALT(
        IF(Interval(today()-FechaVencimiento,'D') <30, 1),
        IF(Interval(today()-FechaVencimiento,'D') <60, 2),
        IF(Interval(today()-FechaVencimiento,'D') <90, 3),
        IF(Interval(today()-FechaVencimiento,'D') <180, 4),
        IF(Interval(today()-FechaVencimiento,'D') <365, 5),
        IF(Interval(today()-FechaVencimiento,'D') <730, 6),
        IF(Interval(today()-FechaVencimiento,'D') <1095, 7),
        8),
   '0 a 1', '1 a 2', '2 a 3', '3 a 6', '8 a 12', '12 a 24', '24 a 36',' más 36 meses') &' meses'  AS RangoDeuda
RESIDENT   FACTURAS; 

Como vemos, el LEFT JOIN se hace leyendo la propia tabla  (LEF JOIN Y RESIDENT tienen como origen y destino la misma tabla), sin necesidad de usar una tabla auxiliar intermedia.

Utilizo ALT() por evitar los IF anidados, que siempre son más engorrosos, pero como ALT() sólo devuelve valores numéricos y no puede devolver directamente el nombre del rango, hay que recoger su valor en un PICK() .

El resultado final del ejemplo sería algo así:

Cuando el usuario elige un rango, la tabla se filtra automáticamente.

► GetObjectDimension y GetObjectExpression

Existen dos funciones en Qlik Sense que estuvieron documentadas en las primeras versiones y, aunque siguen funcionando (verificado hasta la versión de Noviembre de 2018), han desaparecido de la documentación sin dejar rastro.

Son GetObjectDimension(índice) y GetObjectExpresion(índice)

GetObjectDimension(0) Devuelve la etiqueta de la dimensión actualmente seleccionada en un grupo de dimensiones alternativas.

GetObjectExpresion(0) Devuelve la etiqueta de la expresión actualmente seleccionada en un grupo de expresiones alternativas.

Ojo, lo que devuelve es la etiqueta y quizá este sea el motivo de que hayan desaparecido de la documentación. Aunque siguen funcionando, es posible que discontinúen su funcionamiento. Quizá de problemas cuando la etiqueta de la expresión/dimensión es una variable.

Veamos un ejemplo.

Vamos a suponer que queremos un gráfico que acumule las ventas durante un periodo de tiempo. En Sense no existe el Tic de QlikView para acumular, por tanto lo tenemos que hacer con una fórmula.

Para agravar la situación, el gráfico tiene 3 dimensiones alternativas. Mes, Semana  y Día. Para permitir mostrar la información, necesitamos 3 expresiones distintas, una que acumule por Mes, otra por Semana y otra por Día y que cada una se ejecute dependiendo de la dimensión seleccionada.

El resultado debería ser este:

La configuración de datos y expresiones queda como en la siguiente imagen. Observad que hay 3 dimensiones alternativas y sólo una medida.

La expresión de la medida es esta:

ALT(
    IF(GetObjectDimension(0)='Mes Venta',
              SUM(aggr(rangesum(above(SUM({<[Year]={'$(vL.AñoActual)'}>} [Precio Neto]+[Iva Compra]),0,num([Month]))),([Month],(NUMERIC, ASCENDING))))),
    IF(GetObjectDimension(0)='Dia del Año Venta',
              SUM(aggr(rangesum(above(SUM({<[Year]={'$(vL.AñoActual)'}>} [Precio Neto]+[Iva Compra]),0,num([Dia del Año]))),([Dia del Año],(NUMERIC, ASCENDING))))),
    IF(GetObjectDimension(0)='Semana Venta',
              SUM(aggr(rangesum(above(SUM({<[Year]={'$(vL.AñoActual)'}>} [Precio Neto]+[Iva Compra]),0,num([Week]))),([Week],(NUMERIC, ASCENDING)))))
)

Vamos a diseccionarla:

• ·         En lugar de utilizar IF anidados, usamos la función ALT, que es mucho más legible. Cada IF devuelve un valor numérico (si se cumple) o nulo, en cuyo caso el flujo del programa pasa al siguiente IF.
• ·         El valor con el que se compara GetObjectDimension(0) en cada IF es el texto que hayamos puesto en la etiqueta de la dimensión (ver imagen superior).
• ·         $(vL.AñoActual) Es una variable que contiene el año máximo de los seleccionados (=Max(Year)).
• ·         SUM(aggr(rangesum(above(SUM… Es la expresión que calcula toda la suma de ventas acumulada para cada unidad de tiempo (Month, Dia del Año o Week).
• ·         (NUMERIC, ASCENDING) Son dos parámetros (exclusivos de Sense y de View a partir de la versión 12) de la función AGGR que permiten ordenarla. Sin estos parámetros AGGR se ordena según el orden de carga.

► Calendario con periodos

Cuando utilizamos una dimensión de fecha en la que puede haber vacíos de información, lo normal es crear un calendario que nos rellene esos huecos.

El ejemplo típico es la del negocio que cierra fines de semana y un mes en verano. Si utilizásemos como dimensión, por ejemplo, la fecha de las facturas, nos encontraríamos que nos faltarían todos los fines de semana y el mes de agosto completo, lo que hace que cuando mostremos la información nos aparezcan huecos poco atractivos.

Por eso, lo mejor es crear un calendario. Pero yendo un poco más allá, casi siempre nos surgen preguntas como… ¿Cuál es mi beneficio en lo que va de año? ¿Y respecto al mismo periodo del año anterior? ¿Y respecto al mes anterior?... y así infinidad de preguntas similares.

Si sólo tenemos el calendario con años, meses y días, las comparaciones que necesitamos hacer luego en los set analysis de las expresiones son bastante complejos.
Todo se simplifica bastante añadiendo un campo a nuestro calendario que concatene el año y el mes. De esta forma tendremos un campo numérico que podremos ordenar y comparar.

En el ejemplo utilizamos un campo llamado F_Entrada como fecha para enlazar con el resto del modelo y creamos un campo Periodo (YYYYMM)  y PeriodoDia (YYYYMMDD)

FechaInicio:
LOAD
    date(min([F_Entrada]))     as "FechaInicio"
Resident Operaciones;

FechaFin:
LOAD
    date(max([F_Entrada]))    as "FechaFin"
Resident Operaciones;

let vL.MinDate=num(peek('FechaInicio',0,'FechaInicio'));
let vL.MaxDate=num(peek('FechaFin',0,'FechaFin'));

CALENDARIO_MAESTRO:

LOAD date(recno()+$(vL.MinDate)-1, 'DD/MM/YYYY') as "F_Entrada",

    year(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))*100+ month(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))  as Periodo,

    num#(date(RecNo()+$(vL.MinDate)-1,'YYYYMMDD')) as PeriodoDia,

    year(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY')) as "Año",

    week(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'),0,1) as num_semana,

    Date(MonthStart(date(RecNo()+$(vL.MinDate)-1,'YYYYMMDD')),'YYYY MMM')  AS Año_Mes,

    'T' & ceil(month(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))/3) as "Trimestre",

    month(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY')) as "Mes",

    year(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))&'-'&week(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'),0,1) as Año_Semana,

    weekStart(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY')) &'->'& weekEND(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))   as "Semana",

    Day(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))   as "Día",  

    date(RecNo()+$(vL.MinDate)-2,'DD/MM/YYYY')        as "Ayer",

    WeekDay(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY')) as "Día Sem.",

    Text(date(RecNo()+$(vL.MinDate)-1,'WWWW')) as "Día Semana",

    DUAL(month(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))&'-'&Day(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY')),

        month(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))*100+Day(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))) As Mes_Dia,

    TEXT(Date(RecNo()+$(vL.MinDate)-1,'MMMM')) AS Meslargo,

      If( date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') >= YearStart(Today())  AND date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') <= today(),  1,0) as IsInYTD, //ejemplo de uso: Sum( {$<IsInYTD={1}>} Amount )

      If( date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') >= YearStart(addyears(Today(),-1)) AND date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') <= addyears(Today(),-1), 1,0) as IsInPrevYTD,//ejemplo de uso: Sum( {$<IsInPrevYTD={1}>} Amount )

      If( (date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') >= YearStart(Today())  AND date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') <= today())

          OR(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') >= YearStart(addyears(Today(),-1)) AND date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY') <= addyears(Today(),-1)), 1,0) as IsInPrev2YTD

AUTOGENERATE $(vL.MaxDate)-$(vL.MinDate)+1  ;  

Drop table FechaInicio, FechaFin;

► Transponer un Excel con código de script.

Tenemos esta tabla Excel :

Y queremos obtener la siguiente tabla simple. 

Ojo. Con un gráfico de tabla pivotante, con dimensiones Género y Localidad y pivotando esta última y por expresión hacer un SUM(Población), no podríamos obtener la columna “Media”. Sólo lo podemos hacer con una tabla simple. Pero antes tenemos que conseguir poner cada localidad en una columna para poderlas operar entre si.  De esta forma la “Media” siempre se calculará en función de las localidades seleccionadas por el usuario.

Tenemos que conseguir crear en memoria esta tabla:

El “asistente de archivo” de QlikView no tiene la opción de hacerlo, aunque si puede hacer lo contrario, o sea, a través de la  opción de “Tabla cruzada” podemos ir del punto 2 al punto 1.

Tampoco con la opción “trasponer” podemos hacerlo ya que no nos permite elegir qué campos trasponer y cuáles no.

Una forma de hacerlo es con el siguiente script:

// Cargamos los datos del excel

EXCEL_ORIGINAL:

LOAD Género           AS Genero_orig, 

     Población        AS Poblacion_orig, 

     Localidad        AS Localidad_orig

FROM

[C:\Users\jmmayoral\Documents\Qlikview\dummy.xlsx]

(ooxml, embedded labels, table is Hoja1);

// A partir de esa carga, creamos la que será la definitiva, pero sin el campo que vamos a trasponer

// ni el campo de los valores.

// en nuestro caso queda fuera "Localidad" y "poblacion"

TABLA_FINAL:

LOAD Genero_orig      AS Género

RESIDENT EXCEL_ORIGINAL;

// Creamos ahora una tabla con todos los valores distintos del campo a trasponer

//

LOCALIDADES:

Load Distinct Localidad_orig as Localidad

RESIDENT EXCEL_ORIGINAL;

//Con un bucle recorremos cada valor de la tabla

FOR i=1 to NoOfRows('LOCALIDADES')

     LET vLocalidad=Peek('Localidad',-$(i),'LOCALIDADES'); //aquí toma el nombre de la division.

     LEFT JOIN (TABLA_FINAL)

     LOAD  Genero_orig         as Género, //Lista con todos los campos excepto el traspuesto y el del valor

           Poblacion_orig      as [$(vLocalidad)]    //Creamos una columna nueva llamada como la Localidad que estamos procesando

     RESIDENT  EXCEL_ORIGINAL

     WHERE  Localidad_orig = '$(vLocalidad)';

NEXT i

Drop table EXCEL_ORIGINAL, LOCALIDADES;

► Carga del fichero más reciente

Tenemos un repositorio donde se van almacenando ficheros de datos. Uno por día. Cada día hay un proceso que debe cargar sólo el más reciente. Veamos los pasos necesarios para identificar cuál es el último fichero.

Es imprescindible que la fecha esté en el nombre del fichero. En nuestro ejemplo deberemos cargar sólo el 20181107_101611.csv

El script es el siguiente:

// Nos colocamos en el directorio donde se encuentran los ficheros
DIRECTORY 'import'; 

// Leemos todos los ficheros con extensión .csv que encuentra
for each File in filelist ('*'&'.csv')

// $(File) contiene el path absoluto del fichero. Nos queremos quedar sólo con el nombre. Aquí no vale usar la función FILENAME()
// porque esa función sólo se puede usar en una sentencia LOAD, no es una función de script.

//Para ello tomamos el path completo, buscamos la posición del último '\' y cortamos desde ahí hasta el final 
   let fichero = mid('$(File)',index('$(File)','\',-1)+1);

// Ya tenemos el nombre del fichero.
// Verificamos que el nombre sólo tiene números y el caracter '_' (excluyendo la extensión .csv)
// Si lo cumple, metemos el nombre en una tabla
   IF len(purgechar(left('$(fichero)',index('$(fichero)','.csv')-1),'0123456789_'))=0 THEN            
   Ficheros_tmp1:
      LOAD
         '$(fichero)' as Name
         autogenerate 1;

    ENDIF;

// Repetimos los pasos para el restode ficheros   
NEXT File   


//Cuando ya tenemos todos los nombres en la tabla, los ordenamos en orden descendente....
NoConcatenate
Ficheros_tmp2:
LOAD *
  RESIDENT  Ficheros_tmp1
  ORDER BY Name DESC;
 
Drop table Ficheros_tmp1;

//Y nos quedamos con el primero. Lo metemos en una variable....
let FicheroACargar=peek('Name',0,'Ficheros_tmp2');

Drop table Ficheros_tmp2;

//  .... que usamos luego para cargar el fichero en memoria
EXCEL_MASTER:
LOAD campo1,
     campo2,
     .....
     campoN    
FROM
[$(FicheroACargar)] (txt, utf8, embedded labels, delimiter is ';', no quotes)
;

► Añadir campos nuevos a QVDs históricos

Tenemos un repositorio de QVDs con ficheros históricos desde hace varios años Existe un qvd por cada añomes. El Cuadro de Mandos los lee todos cada vez que se recarga.

En un momento dado, el origen de datos cambia e incluye un par de campos más que deben mostrarse en la aplicación. Los nuevos QVDs incluirán estos campos, pero ¿cómo evitar que la recarga falle cuando vaya a buscar esos campos en los ficheros históricos?

Existen varias soluciones:

1.- Modificar el script para que tenga en cuenta la fecha del .qvd y según esa fecha, cargue los nuevos campos o no.

2.- Modificar el script para crear una carga incremental de modo que una todos los .qvd en uno solo. En cada nueva recarga el CdM leerá ese único .qvd, añadirá nuevos campos cada vez que sea necesario y volverá a generarlo sobreescribiendo el anterior. De este modo podríamos ir añadiendo nuevos campos siempre que lo necesitemos.

3.- Añadir esos nuevos campos vacíos a cada .qvd histórico. El siguiente script lo hace:

SET vL.QVDPath = .\Empleados*.qvd;    //<-- Introducir aquí La ruta de los .QVDs de origen

FOR each File in filelist(vL.QVDPath)

    dummy:
    LOAD filename() as nombreFichero
    FROM [$(File)] (qvd)
    WHERE RECNO()=1;

    Let NombreFichero = Peek('nombreFichero',0,'dummy');

    Drop table dummy;

    $(NombreFichero):
    LOAD *,
         '' AS nuevo_campo1,
         '' AS nuevo_campo2  //Lista de campos nuevos
    FROM [$(File)] (qvd);
       
    Store $(NombreFichero)  INTO  [$(File)] (qvd);

    Drop table $(NombreFichero);   

NEXT File; // Siguiente fichero de entrada

Contenido .QVD Antes: 

Contenido .QVD después

► Cálculos de periodos de Vigencia

A menudo nos encontramos con que tenemos una entidad que tiene fecha de inicio y fecha fin.

Ej: Fechas de inicio y fin de una póliza de seguros, Fecha de alta y Baja de un abonado, Fecha de creación y desaparición de una empresa… etc

Y nos surge la necesidad de ver si esa entidad estaba activa en un año o mes determinado. Se puede hacer creando expresiones con set analysis que comprueben ambas fechas, pero la forma más cómoda es calculando todas las fechas en el script.

Para ello creamos una tabla auxiliar que contenga las fechas de vigencia de la entidad en cuestión.

Vamos a ver el ejemplo de fechas de vigencia de Pólizas donde existe una tabla POLIZAS con los campos %POLIZA como clave única,      F_ALTA_POLIZA  como Fecha de inicio y F_BAJA_POLIZA como fecha de fin.

TMP1:

LOAD DISTINCT

     %POLIZA,

     F_ALTA_POLIZA  AS FechaInicio,

     RANGEMIN(F_BAJA_POLIZA,TODAY()) AS FechaFin //Si no tiene fecha de baja, las limitamos a hoy o al fecha que queramos.

RESIDENT POLIZAS;

FECHAS_VIGENCIA_POLIZA:

LOAD

  %POLIZA,

  Year(AddMonths(FechaInicio, IterNo() - 1)) AS AÑO_VIG_POLIZA,

  Month(AddMonths(FechaInicio, IterNo() - 1)) AS MES_VIG_POLIZA,

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_VIG_POLIZA

Resident TMP1

While Num(Year(AddMonths(FechaInicio, IterNo() - 1)) * 100 +

       Month(AddMonths(FechaInicio, IterNo() - 1))) <=

       Num(Year(FechaFin) * 100 + Month(FechaFin))

;

DROP Table TMP1;

Usamos la función DUAL para obtener el AÑOMES_VIG_POLIZA en los formatos

Ene-2018 (texto) y 201801 (número). De esta forma en un selector podemos usar la representación de Texto, más amigable para el usuario, y en una expresión podemos usar el formato numérico.