Cestas o canastas de compras, table calc, LOD calc, Workout Wednesday

Por:Rosario Gauna @rosariogaunag

En la semana 14 de Workout Wednesday, @AnnUJacksonnos preparó un reto de matriz de frecuencias. Link al Reto

Este reto me recordó al de la semana 42 del año pasado publicado por @EmmaWhyte, referente al análisis de cestas de compras. Aunque las gráficas presentadas en ambos retos son diferentes, el procedimiento básico de solución es muy similar.

El análisis de cestas de compras es una solicitud común, particularmente en ciertas industrias como el comercio minorista en línea, para realizar sugerencias sobre qué otro producto podría interesar al cliente al momento de su compra.

A continuación, enlistaré los principales pasos requeridos:

Paso 1: Crear un INNER-JOIN de la tabla de datos de “Orders” en Excel de SuperStore consigo misma

La gráfica solicitada en este reto es una matriz simétrica de frecuencias del número de órdenes para cada combinación de Sub-Categorías. Por las dimensiones de la matriz se percibe la necesidad de realizar un INNER-JOIN de la tabla de datos consigo misma a través de la dimensión de “Order ID”.

Picture2

Las variables de la segunda tabla utilizada en el JOIN (Orders1) se diferenciarán de la tabla original con la terminación (Orders1).

Paso 2: Calcular el número de órdenes únicas que se venden por cada combinación de Sub-Categorías. Este es el cálculo de la frecuencia de órdenes.

Unique Orders = COUNTD([Order ID])

Paso 3: Obtener el promedio de ventas para cada una de las Sub-Categorías, de la combinación seleccionada.

Average Sales = SUM({FIXED [Row ID] : AVG([Sales])}) / [Unique Orders]
Average Sales (Orders1) = IIF(ATTR([Sub-Category]) = ATTR([Sub-Category (Orders1)]), NULL, SUM({FIXED [Row ID (Orders1)] : AVG([Sales (Orders1)])}) / [Unique Orders])

Considero que las fórmulas de este paso son cruciales para lograr obtener los números correctos, por lo cual profundizaré en su explicación y su relación con el funcionamiento del INNER-JOIN.

Cuando se utiliza la técnica del INNER-JOIN de una tabla consigo misma, se debe tener precauciónsi el análisis se realiza sobre una dimensión agregada, i.e. una Sub-Categoría puede tener varios registros de productos diferentes comprados dentro de una misma orden o cesta y hace que los detalles de la segunda tabla del INNER-JOIN se repitan a consecuencia del JOIN.

Si el reto solicitado tratara de combinaciones de productos en lugar de combinaciones de Sub-Categorías, las fórmulas requeridas serían bastante sencillas debido a que cada renglón representa un producto y a nivel producto no hay duplicidad. Sin embargo, en este reto el análisis se realiza a nivel de combinación de Sub-Categorías.

Picture3

La tabla anterior, muestra un ejemplo del JOIN resultante de la Orden “CA-2014-103317”. De la Sub-Categoría “Furnishing” se venden dos productos diferentes registrados en los renglones 5437 y el 5439 y para la Sub-Categoría (Orders1) de “Binders” se vende un solo producto registrado en el renglón 5438.

Cuando se desean analizar las ventas “Furnishing” con “Binders”, si se tomaran las ventas directamente de la Sub-Categoría de Orders1 de “Binders” se estarían duplicando la venta. Es decir, existe una repetición por cada producto diferente vendido de “Furnishing”. Ver Row ID 5438 de (Orders1) del lado derecho en color rojo.

Por lo cual, para asegurar que la información se considere sin duplicidad, se deben calcular inicialmente las ventas promedio por cada “Row ID” único con la siguiente fórmula {FIXED [Row ID] : AVG([Sales])}

Picture4

El siguiente paso es obtener el promedio de las ventas por Sub-Categoría para la combinación seleccionada, las cuales se obtienen de sumar las ventas promedio por cada Row-Id y el resultado se divide entre el número de órdenes únicas para la combinación de Sub-Categorías seleccionadas.

¿Por qué realizo el cálculo manual del promedio y no utilizo la fórmula de “Average”?Porque si aplicáramos la fórmula de average, los resultados obtenidos serían de las ventas promedio por Row-Id, y el reto solicita las ventas promedio por orden de cada Sub-Categoría.

Adicionalmente, a la fórmula de (Orders1) se le incluye la condición para que el valor sea nulo en caso de que la Sub-Category y la Sub-Category (Orders1) coincidan. En la diagonal se considera sólo un promedio de ventas.

Paso 4: Calcular el nombre de las Sub-Categorías en mayúscula e iniciar la definición de la gráfica:

Upper Sub-Category = UPPER([Sub-Category])
Upper Sub-Category (Orders1) = UPPER([Sub-Category (Orders1)])

Colocar las variables “Upper Sub-Category” en Rows y “Upper Sub-Category (Orders1)” en Columns (Si lo prefiere puede crear las fórmulas directamente en los estantes de renglones y columnas sin necesidad de definirlas como nuevas variables).

Ordenar descendentemente ambas variables, por el número de órdenes únicas obtenidas en el paso dos.

Seleccionar Square en el tipo de Mark.

Paso 5: Personalizar el texto desplegado, los tooltips y el color dependiendo si se está en la línea diagonal o fuera de la línea diagonal.

A) Calcular la etiqueta de Texto desplegado en la diagonal de la matriz

Esta etiqueta se mostrará sólo cuando la SubCategory seleccionada y SubCategory (Order1) coincidan.

Label Diagonal = IIF(ATTR([Sub-Category])=ATTR([Sub-Category (Orders1)]),[Unique Orders],NULL)

Mover al estante de Text la variable recién creada.

B) Personalizar los tooltips de la diagonal vs. el resto de la matriz

Picture5

Las siguientes variables, sólo mostrarán información cuando la SubCategory seleccionada y SubCategory (Order1) son diferentes.

Label SubCategories A & B = [Sub-Category] + IIF([Sub-Category]<>[Sub-Category (Orders1)], ” & ” + [Sub-Category (Orders1)],””)
Label SubCategories B =  IIF([Sub-Category]<>[Sub-Category (Orders1)], [Sub-Category (Orders1)] + “:”,NULL)

Estas variables, más la de “Unique Orders”, “Sub-Category”, “Avg Sales” y “Avg Sales (Orders1) deberán ser incluidas en el estante de tooltips.

C) Personalizar el color de la diagonal vs. el resto de la matriz

Color = IIF(ATTR([Sub-Category])<>ATTR([Sub-Category (Orders1)]), [Unique Orders], -20)

El reto nos solicita utilizar el color morado en las celdas de la matriz que se encuentren fuera de la línea diagonal y usar las tonalidades más obscuras para la frecuencia de órdenes de mayor valor.

En mi primera versión yo utilicé el color azul, me gusta el color azul. Ahora utilizaré el color morado.

Necesitamos un pequeño truco para lograr esto.

El primer paso a seguir es seleccionar la tonalidad Red-Blue-White Diverging.

No crean que olvidé lo que acabo de decirles o que se trata de una broma, vamos a presentar la matriz en color morado, aunque prefiera el azul.

La selección Red-Blue-White Diverging es solo un paso para lograr el color morado, ya que no existe una opción de morado divergente predefinida que pueda seleccionar.

El siguiente paso es modificar el color azul por morado, para lo cual, simplemente se requiere dar un doble-click al color azul e indicarle el color que lo reemplazará (yo utilicé el color “990099”)

Por último, en las opciones avanzadas de color, se establece el centro en un valor de -20, que es el valor que la fórmula establece para de todas las celdas de la diagonal, para obligar que al valor de -20 le corresponda el color de blanco.

Picture6 

Paso 6: Obtener la combinación con mayores ventas promedio por orden y personalizar el Texto y los tooltips.

Personalizar el estante de Texto para incluir un punto, en la celda de mayores ventas promedio.

Label Dot = IIF(WINDOW_MAX([Avg Sales] + [Average Sales (Orders1)])=[Avg Sales] + [Average Sales (Orders1)], “●”, “”)

Personalizar su tooltip para incluir una leyenda, en la celda con mayores ventas promedio.

Label Dot 2 = IIF(WINDOW_MAX([Avg Sales] + [Average Sales (Orders1)])=[Avg Sales] + [Average Sales (Orders1)], “highest average sales per order”, “”)

Paso 7: Ajuste formatos. ¡Y listo!

Link Tableau Public

Picture1

Espero que les sea de utilidad este blog.

Si tiene alguna pregunta, no dudes en ponerte en contacto conmigo en Twitter (@rosariogaunag)

¡Saludos!

Rosario Gauna

Advertisements