► 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.

► Cómo evitar IF anidados

Cuando tenemos que aplicar expresiones distintas en función del valor que tome una dimensión, podemos hacerlo de varias formas.

Ej: If (dimension =1, expr1,

        If (dimension=2, exp2,

            If (dimension=3, exp3,

                If (dimension=x, exprx))))

      1) Usando la función PICK(). Esto sólo lo haremos cuando los valores de la dimensión sean números naturales.

            Pick(dimensión, expr1, expr2, expr3……exprx)

Una forma rápida de crear esta función pick es hacerlo dinámicamente usando CONCAT() en lugar de escribir una función larguísima que contenga todas las expresiones. Así mejoramos el mantenimiento y la legibilidad.

Para hacerlo, hay que meter todas las expresiones en un campo en una isla de datos. El origen puede ser un inline, una Excel o un fichero de texto. Lo único que hay que tener en cuenta es que deben estar ordenadas según el valor de la dimensión y el orden que ocuparán en la función PICK().

Ejemplo: Imaginemos que tenemos una dimensión “Dim_x” con los valores 1, 2 y 3 y que según el valor que tome se aplique una expresión u otra: Para el valor 1 hará la suma del campo1, para el valor 2 hará la media y para el valor 3 obtendrá la moda.

Tenemos este inline:

ISLA_DATOS:

LOAD * INLINE [

Orden,    Expresion

1,        SUM(campo1)

2,        AVG(campo1)

3,        MODE(campo1)

];

En las expresiones de gráficos u objetos sustituiremos los if anidados por esta única expresión:

PICK(Dim_x, $(=CONCAT(Expresion, ’,’ ,Orden)))

Observad que los argumentos de CONCAT son las columnas del INLINE

 

    2)   Con expansión del signo $. De esta forma los valores de la dimensión no tienen por qué ser numéricos.

A diferencia del ejemplo 1 en el que las diferentes expresiones se introducían en un campo de una isla de datos cuyo origen era un inline, un Excel o cualquier otra fuente, aquí crearemos una variable por cada expresión.

El nombre de cada variable debe tener un formato muy concreto: una raíz común + cada valor posible de la dimensión:

Ejemplo 1: Suponiendo que tenemos una dimensión “Dim_x” con los valores 1, 2 y 3

Creamos las variables:

       SET Var_1 = SUM(campo1);

      SET Var_2 = AVG(campo1);

      SET Var_3 = MODE(campo1);

Ejemplo 2: Suponiendo que la dimensión “Dim_x” tenga los valores Madrid, Málaga y Bilbao

Creamos las variables

SET Var_Madrid = SUM(campo1);

          SET Var_Málaga = SUM(Campo1*0,05);

          SET Var_Bilbao =  SUM(campo1*0,02);

En los objetos sustituimos los if anidados por esta expresión:

                             =$(='Var_'&ONLY(Dim_x))


3)  Usando la función ALT().

Esta opción es la menos recomendable porque se ejecuta casi igual que los IF anidados, pero al menos facilita la lectura y comprensión de la expresión. 

Se debe usar sólo cuando no se puedan evitar los IF anidados, como por ejemplo en el Script.
Sólo funciona cuando los valores devueltos son números. No funciona si devuelve cadenas de caracteres.

Usando el ejemplo 2, lo podemos transformar por:

 ALT(
      IF(Dim_x=’Madrid’, SUM(campo1)),
      IF(Dim_x=’Málaga’, SUM(Campo1*0,05)),       
      IF(Dim_x=’Bilbao’, SUM(campo1*0,02)),
      0
    );