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.
