Cómo usar LODs para calcular medias ponderadas

Seguro que no soy el único que al que le costó tiempo perderles el miedo a los cáculos LOD en Tableau cuando aparecieron por primera vez. Me llevó un tiempo considerable acostumbrarme a utilizarlos en el día a día y sacarles partido pero ahora soy cada día más fan y les encuentro más y más usos. No solo porque te permiten hacer cosas en Tableau que antes no podías, que también, pero sobre todo porque permiten realizar cosas mucho más rápido y sencillo que anteriormente. Hoy voy a compartir un ejemplo de estos últimos de como usar un cálculo LOD usando la función EXCLUDE para obtener el mismo cálculo que en una media ponderada.

He estado trabajando los últimos dos días en una visualización sobre la polución en Madrid usando datos públicos del Ayuntamiento (puedes consultar los datos en este enlace). Concretamente datos de la presencia de dióxido de nitrógeno en la atmósfera en distintas areas de Madrid. Cada registro en la base de datos contiene las medidas de las 24 horas del día para cada día del año y cada estación de medición. Tras una rápida preparación de los datos en Alteryx para darles una estructura más fácil de usar en Tableau (cada hora no en distintas columnas sino en una fila distinta), dar un formato a la fecha y añadir varios campos de referencia como la latitud y longitud de la estación remota, el nombre de la misma, la unidad de medida del dióxido de nitrógeno, etc. Y tras un par de dias trabajando en la visualización, este es el resultado final.

Pero para este post quiero centrarme en una parte concreta del dashboard. Esta en concreto:

El gráfico muestra la media de dióxido de nitrógeno en Madrid cada hora (de 12 de la madrugada a 11 de la noche – de izquierda a derecha -) los días de diario (círculos negros) y fines de semana (círculos verdes). La línea conectando ambos puntos es símplemente para resaltar la diferencia entre ambos valores y la línea horizontal es la media a dicha hora. Se puede ver que la media de cara hora es siempre más cercana a los valores de los dias de diario debido a que cada semana tiene 5 dias de diario y 2 de fines de semana. La media por tanto no es solo el la suma de los dos puntos divida entre dos, sino que es una media ponderada en base al número de días en cada marca ¿Cómo podemos crear ese cálculo de una manera rápida y sencilla?

Antes de explicar el sencillo cálculo que requiere, voy a explicar brevemente cómo crear el gráfico:

  1. Usando la fecha disponible (la fecha de la medición de contaminación) para crear un nuevo campo que me devuelva el día de la semana. Hacemos click derecho en el campo > Create > Custom Date y seleccionamos Weekdays como date part.
  2. Creamos un nuevo campo agrupando los valores del campo que acabamos de crear. El primer grupo correspondería a los días entre semana (lunes a viernes) y el segundo a los fines de semana (sábado y domingo).
  3. Ahora queda construir el gráfico, que es bastante sencillo. Incluimos la hora de nuestra fecha en columnas, nuestro valor en filas dos veces, uno con el tipo de marca = línea y el segundo con el tipo de marca = círculos con el campo que hemos creado en el paso número 2 en la tarjeta de Color.

Tenemos casi la visualización que queremos. Pero si añadimos una línea de referencia que sea la media para cada celda desde el menú de Analytics, obtenemos lo siguiente:

¿Es correcto? bueno, parece que Tableau lo que está haciendo en este caso es sumar el valor de los días entre semana y el de los fines de semana, y dividiendo dicha suma entre 2 ¿Pero es esa realmente la media real de dióxido de nitrógeno de cada hora? Vamos a crear otro gráfico para ver la media en cada hora y comprobarlo.

Los valores son parecidos… pero no exactamente iguales ¿Por qué? Porque hay 5 días entre semana y 2 días de diario, por lo que la media de todos los días de la semana no es la que Tableau realiza por defecto cuando incluimos la media directamente como línea de referencia. Queremos tener en cuenta el hecho de que existe un mayor número de días de diario en la semana a la hora de calcular la media. Es decir, si tenemos los valores para dias de diario y fines de semana, queremos la media ponderada por el número de días en cada una de esas dos categorías.

¿Cómo podemos hacer esto en Tableau? Antes de existir los cálculos LOD tendríamos que calcular el número de días dentro de cada grupo, y utilizarlo como divisor por ejemplo. Pero existe una manera mucho más elegante, rápida y sencilla con un LOD usando EXCLUDE. si creamos un campo calculado excluyendo el campo agrupado que hemos creado anteriormente y calculamos la media de dicho valor así:

AVG({EXCLUDE [Weekdays Group] : AVG([Value])})

Incluímos dicho cálculo en el campo de detalle de la visualización y utilizamos dicho campo como una línea de referencia, obtenemos lo siguiente:

Si comparamos los valores de esta línea de referencia con los del gráfico de barras que hemos creado anteriormente, veremos que son iguales. El cálculo LOD, en lugar de calcular la media de dióxido de nitrógeno al nivel de agregación que tenemos en la visualización (el tipo de día de la semana) está excluyendo dicho nivel y calculando la media a un nivel menos agregado. Es decir, está comportándose como si realizaramos una media ponderada, por lo que la línea de referencia se encuentra más cercana a los valores de los días entre diario al tener 5 de los 7 días de la semana y obtenemos el valor que queríamos de una manera sencilla y rápida.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *