Agregación en tablas simples, MTD, YTD

 A veces tenemos la necesidad de crear una columna que vaya sumando los resultados de las filas anteriores.

Qlik Sense tiene una opción que nos permite hacer esto sobre una columna, 

el problema es que en la primera fila escribe 0, y no el primer valor que debería acumular. además tampoco permite acumular en sentido descendente.

Imaginemos esta tabla con 2 columnas llamadas Fecha Contable y Sales a la que queremos añadir una columna MTD (Month To Date) que acumule mes a mes:

Lo primero que vemos es que está ordenada descendentemente por fecha. Y debemos tener en cuenta que cada mes el contador deberá ponerse a 0, o sea, no acumulará entre meses.

La expresión que debemos usar en la nueva columna es: 

AGGR(RANGESUM(BELOW(SUM(Sales), 0, DAY([Fecha Contable]))),([Fecha Contable],(NUMERIC, DESCENDING)))

Vamos a analizarla: 

BELOW(SUM(Sales), 0, DAY([Fecha Contable])) --> Suma todas las ventas (Sales) desde la fila actual (indicada por el parámetro 0) hasta n filas anteriores. Estas n filas anteriores vienen dadas por el parámetro DAY(Fecha Contable) de modo que si estamos en el día 17 de un mes, sumará desde el día 17 hasta 17 filas hacia abajo, o sea, hasta el día 1.

AGGR (RANGESUM (......,([Fecha Contable],(NUMERIC, DESCENDING))) --> Hace el cálculo para cada fecha de la tabla. El primer parámetro de AGGR es la función que queremos agregar, en este caso RANGESUM.... El segundo parámetro de AGGR indica el campo por el que se deben hacer los grupos, el tipo de este campo y cómo está ordenado. En nuestro caso la columna que usamos para agrupar es la propia Fecha Contable, que es de tipo numérico y la hemos ordenado descendentemente.

El resultado es el siguiente: 

Obsérvese cómo va acumulando los valores de cada fecha hasta que llega el día 1 del mes siguiente. entonces reinicia la suma para empezar a acumular de nuevo.

En caso de que la tabla estuviera ordenada de manera ascendente, o sea, como se ve aquí, 

La expresión hay que "darle la vuelta" porque ahora cada fila acumula las filas superiores, o sea, ahora la función es ABOVE(),  y también cambia el sentido de ordenación de AGGR de "DESCENDING" a "ASCENDING"

AGGR(RANGESUM(ABOVE(SUM(Sales), 0, DAY([Fecha Contable]))),([Fecha Contable],(NUMERIC, ASCENDING)))

Si en vez del campo "Fecha Contable" usamos cualquier otro, como por ejemplo "Mes" (suponiendo que tengamos un campo Mes asociado a cada fecha contable), sólo veríamos el cálculo el día 1 de cada mes.

En caso de que quisiéramos acumular año a año (YTD - Year To Date), basta con sustituir la función DAY() por DAYNUMBEROFYEAR(). En este ejemplo vemos la expresión usada cuando el orden es descendente.

aggr(rangesum(BELOW(SUM(Sales), 0, daynumberofyear([Fecha Contable]))),([Fecha Contable],(NUMERIC, DESCENDING)))

Atención: Todo lo anterior sólo sirve en caso de que que la primera fecha comience el día 1 del mes y no haya huecos entre fechas.