Tabla comparando un día contra el mismo día del año anterior

Un problema recurrente en nuestros clientes es la necesidad de evaluar unos determinados KPIs diariamente.

Para ello solemos crear una tabla simple, ordenada descendentemente por un campo fecha y a continuación los KPIs solicitados. Por ejemplo: ventas, nº de pedidos, nº de artículos vendidos... etc.

El problema viene cuando el cliente te dice que al lado de cada KPI quiere ver el mismo, pero justo del mismo día del año anterior.

No podemos usar un set analysis para esta columna del año anterior porque el set analisys devuelve un grupo de registros y no hay forma de comparar una fecha contra si misma del año anterior. O sea, el 15/05/2023 nunca va a ser igual al 15/05/2022.

Vamos a suponer que tenemos una tabla con la "Fecha Venta", "Año" e "Importe de Venta"

Aquí se dan 2 situaciones:

1ª

Si el rango de fechas está completo, o sea, no falta ningún día y si por más selecciones que haga el usuario nunca va a faltar ningún día, se puede crear una tabla simple que tenga "Fecha Venta" como dimensión, SUM([Importe de Venta]) como una columna y para la comparación del año anterior hacer otra expresión con:

ALT(Below(Sum({<[Fecha Venta]=, Año>} [Importe Venta]), 365) * avg(1), 0)  

donde lo único que hacemos es tomar el registro nº 365 antes del actual en la tabla que el gráfico genera en memoria. Si la ordenación fuera de menor a mayor, deberíamos usar Above en vez de Below.

Pero poniendo 365, así, tal cual, vamos a tener un pequeño problema con los años bisiestos. Tendríamos que contar 365 días unas veces y otras tendríamos que contar 366. Esto lo podemos solucionar con esta expresión:

ALT(Below(Sum({<[Fecha Venta]=, Año>} [Importe Venta]), [Fecha Venta] - makedate(year([Fecha Venta])-1, month([Fecha Venta]), day([Fecha Venta])))*AVG(1),0)  

donde la parte en rojo lo único que hace es calcular una fecha con el mismo día, mismo mes del año anterior (year([Fecha Venta])-1) y esa fecha se la resta a la del año en curso. De esta forma qlik hace el cálculo de los días transcurridos y unas veces restará 365 u otras 366.

2ª

Si tenemos huecos, o si estos se generan cuando hacemos selecciones (por ejemplo seleccionar un artículo concreto y que se cumpla la condición de que no haya habido ventas ese día de ese artículo), ya no sirve restar 365 días porque nos vamos a ir a otra fecha con total seguridad... incluso de otro mes.

En este caso tenemos que modificar un poco nuestra tabla:

1.- crear un calendario maestro con los campos "Fecha Venta", "Año", "IsInPrev2YTD", "Mes_dia"

donde :

IsInPrev2YTD tiene '1' si la fecha está en los 2 años (actual y anterior). Tiene que estar en los 2. No basta que esté solo en el anterior. Así evitamos que, si estamos en noviembre del 2023, la tabla muestre los días de diciembre de 2022 en la columna dimensión.

Mes_dia es la concatenación del nombre del mes y el día: ejemplo Feb.-13

Por supuesto estamos dando por hecho que nuestra tabla mostrará sólo datos de este año y del anterior.

2.- Creamos también 2 variables para el año actual y el anterior: 

vL.AñoActual = year(MAX([Fecha Venta])

vL.AñoAnterior = year(MAX([Fecha Venta])-1

3.- Cremos una tabla simple:

Como dimensión ponemos el campo Mes_dia, pero así solo, también mostraría los días de diciembre de 2022 (recordad que suponemos que estamos en noviembre de 2023), y no queremos que esas fechas aparezcan en el listado.

por lo tanto la dimensión será esta expresión: =IF(IsInPrev2YTD,Mes_Dia) que comenzará a mostrar datos desde el 1 de enero del año actual hasta hoy.

La primera expresión serán las ventas del día: SUM({<Año={$(=$(vL.AñoActual))}>}  [Importe Venta])

La segunda expresión, la del año anterior, será esta:

Sum({<Año={$(=$(vL.AñoAnterior))}, IsInPrev2YTD={1}>} [Importe Venta])

De esta forma le decimos que tome los días del año anterior marcados con IsInPrev2YTD. Los días de dicembre de 2022 no están marcados porque no existen todavía los de diciembre de 2023 y DEBEN EXISTIR EN LOS 2 AÑOS. 

Al seleccionar la columna Mes_dia, estamos tomando valores independientes del año, y ese valor se repite en los mismos días de cada año, por lo tanto sí podemos compararlo, no como ocurre cuando tomamos la fecha completa. O sea, para 13/02/2023 y 13/02/2022 el valor de Mes_dia es el mismo: Feb.-13

Para comparar mes con mes del año anterior, el proceso es similar. preo esta vez en lugar de tomar como dimensión Mes_dia, tomamos Meslargo (el nombre completo del mes, que estará definido en nuestro calemdario maestro). =IF(IsInPrev2YTD, Meslargo)

Y ahora no es necesario que las expresiones incluyan IsInPrev2YTD={1}. Podemos usar Sum({<Año={$(=$(vL.AñoActual))} >} [Importe Venta]) y Sum({<Año={$(=$(vL.AñoAnterior))} >} [Importe Venta]).

Para el caso de comparar semana a semana, la dinámica es la misma, solo que ahora usaremos el campo num_semana (definido en el calendario maestro) =IF(IsInPrev2YTD, num_semana)  y las expresiones son exactamente las mismas que en el caso de los meses.

Aquí conviene introducir 2 nuevas columnas que muestren el rango de días que tiene cada semana ya que no van a coincidir entre los 2 años PAra ello usaremos 3 nuevos campos definidos en el calendario maestro:

• Semana, que tiene el rango de días de la semana
• IsInYTD que indica si el día está en este año y ya ha pasado.
• IsInPrevYTD que indica si el día está en este año anterior y ya ha pasado respecto al año actual. siguiendo con nuestro ejemplo, los días de diciembre 2022 están a 0 porque todavía no han pasado los de diciembre 2023. O sea, va indicando los mismos días transcurridos del año anterior respecto a este año.

La expresión para la columna de este año es: ONLY({<IsInYTD={1}>} Semana)

y para la columna del año anterior es:ONLY({<IsInPrevYTD={1}>} Semana)

El resultado se verá algo así:

comparacion de un rango de fechas contra cualquier otro

Muchas veces tenemos que comparar una fecha con otra en otra periodo o incluso con otro periodo completo, como por ejemplo el balck Friday, o las fechas de una campaña promocional contra otra campaña que haya durado días diferentes.

En el siguiente ejemplo partimos de que en nuestro modelo tenemos un campo llamado "Fecha Compra" que es el que nos dará las fechas a comparar.

La forma de hacerlo es:

1.- Creamos 2 estados alternos. A uno le llamamos "estudio", y a otro "comparacion".

2.- Creamos un juego de selectores para la fecha de estudio: 

Cada uno de estos selectores lo asociamos al estado alterno "estudio". Basta con ir al las propiedades de cada objeto --> Aspecto --> Estados alternos y seleccionar ahí el estado "estudio"

Para verificar que el rango de fechas es correcto, podemos crear un KPI que represente el rango de fechas:

La expresión de este KPI es: 'Del '&MIN({estudio} [Fecha Compra])&' al '&MAX({estudio} [Fecha Compra])

3.- Del mismo modo creamos otro juego distinto de selectores para la fecha de comparación y cada objeto lo asociamos al estado "comparacion".

 y creamos un nuevo KPI para ver el rango de fechas seleccionado.

cuya expresión es: 'Del '&MIN({comparacion} [Fecha Compra])&' al '&MAX({comparacion} [Fecha Compra])

De este modo compararemos los datos del rango del estado "estudio" con los datos de las fechas del rango "comparacion".

Ahora crearemos KPIs para mostrar información de cada rango de fechas:

Para los KPIs que utilicen la fecha de estudio, introduciremos en el set análisis: 

[Fecha Compra]=p({estudio} [Fecha Compra])

Por ejemplo, para saber las ventas de ese periodo, basta con usar la expresión:

SUM({< [Fecha Compra]=p({estudio} [Fecha Compra])>} Sales)

donde estudio es el estado alterno que queremos usar.

Para saber las ventas en el periodo de comparación, la expresión sería:

SUM({< [Fecha Compra]=p({comparacion} [Fecha Compra])>} Sales)

donde comparacion es el estado alterno que queremos usar.

4. -A partir de aquí podemos comenzar a crear nuestros gráficos, en los que siempre incluiremos 2 expresiones, una para el periodo de estudio y otra para el periodo de comparación, con la misma estructura que los ejemplos de ventas que acabamos de ver.

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.

Cálculos al final de cada semana

En esta ocasión nos piden  un gráfico de líneas que nos muestre el stock de un almacén al final de cada semana.

Por cada día, disponemos de un solo registro que acumula todos los movimientos de día, y tiene la siguiente información:

día --> día que se producen los movimientos

Stock_final --> stock al final del día.

Hacer el gráfico por días es muy simple, pero la dificultad viene al hacerlo por semanas. No se puede utilizar una función de agrupación que sume los stock de los días de la semana.

En primer lugar crearemos una variable que almacene la última fecha que tenemos stock. La llamaremos vL.dia_ultimo_stock

En segundo lugar, debemos identificar la semana a la que pertenece cada día. Lo mejor es añadir la siguiente línea en el calendario maestro del script:

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

o esta otra línea en cada registro de la tabla que contiene el campo día:

 year(date(día,'DD/MM/YYYY'))&'-'&week(date(día ,'DD/MM/YYYY'),0,1) as Año_Semana

Como el gráfico es un evolutivo por semana, no podemos mezclar semanas de distintos años, por eso concatenamos año con el nº de la semana.

Para quedarnos con el stock del último día de la semana, tenemos que identificar cuál es ese día. Aquí es donde vienen las dificultades:

• Si tomamos como último día de la semana el día 6 (suponiendo que el primer día sea el 0), es muy posible que la última semana del año no termine el día 6 y que ese día (normalmente el domingo) esté en la primera semana del año siguiente. Por lo tanto tendremos que identificar también el último día del año. Si no lo identificamos, el gráfico descenderá en esa semana hasta el valor 0. 

En esta imagen y en la siguiente observamos cómo la última semana de 2021 y la última de 2022 no terminan en domingo. como no tenemos datos del domingo, el gráfico desciende hasta el cero

• Y en el extremo contrario, es posible que el día en curso no corresponda tampoco al último día de la semana, por lo que el gráfico también descenderá hasta el valor 0. Por tanto tendremos que identificarlo también.

Este gráfico se calculó un miércoles, mientras que el último día de la semana será el domingo, como todavía o hay datos del domingo, el gráfico desciende hasta el cero

Para evitar todos estos problemas, debemos crear esta expresión de gráfico

SUM(if(día=floor(yearend(día)) OR día=floor(weekend(día)) OR día=$(vL.dia_ultimo_stock), stock_final, 0))

(Observese que tanto yearend() como weekend() devuelven el último segundo del año y semana respectivamnete, por eso truncamos la fecha con floor() )

De esta forma identificamos cuál es el último día de cada año, el último día de cada semana y el día actual y obtenemos el stock final de cada uno de ellos.

Ahora los cálculos para las últimas semanas de cada año y para la semana actual son correctos

Se debe tener en cuenta que de esta forma tendremos semans con menos de 7 día. Por ejemplo, la última semana de 2021 (semana 53) sólo tiene 5 días (de lunes a viernes) y la semana 1 de 2022 sólo tiene 2 días (sábado y domingo)