Escrito por: Robert Crocker, Rosario Gauna y Klaus Schulte
Descargar datos aquí
La visualización de datos financieros ha sido un tema importante en recientes blogs.
- Visualización de Estado de Resultados refleja el uso general visual en el contexto de las pérdidas y ganancias y ofrece ejemplos de malas y buenas prácticas
- Incluir Subtotales Personalizados en Tableau describe cuatro soluciones para combinar las calculas de agregaciones normales y los cálculos de la tabla de los sub-totales en una sola visualización, un requisito también necesario en el contexto del Estado de Resultados.
- Múltiples niveles de Drill-Downs / Drill-Ups una nueva Técnica Drill-Down & Drill-Up introducida por Rosario para lograr un análisis exploratorio a través de múltiples jerarquías.
En este contexto, esta publicación se añade a las publicaciones anteriores y presenta un Estado de Resultados con sub-totales personalizados y con el uso de Drill-Down & Drill Up, para permitir un análisis exploratorio dentro de una jerarquía típica de cuenta.
Este es un trabajo conjunto de Robert Crocker, Rosario y Klaus.
1 Contexto
Las finanzas son la última industria en la que me hubiera imaginado trabajar, pero aquí estoy. Durante los últimos 7 meses he estado felizmente contratado por una empresa financiera.
A primera vista, sabía que estos magos de Excel eran conscientes de cosas que no podía percibir por sus datos tabulares. Sólo después de pasar meses postergando productivamente el diseño de los tableros existentes, pude enfrentar la abrumadora colección de tablas en las que estos analistas viven día a día.
Coincidentemente, fue entonces cuando Klaus y Rosario comenzaron a compartir estos invaluables posts enfocados en agregar valor a mi audiencia sin despojarlos de sus tablas. Resulta que las tablas tienen una forma única y valiosa en el contexto del Estado de Resultados. Desafortunadamente, Tableau se queda corto en proporcionarnos todo lo que necesitamos para presentar datos tabulares.
Afortunadamente, los hackers como Klaus y Rosario son lo suficientemente diestros en el uso de Tableau y Finanzas para añadir la función de la que carece esta forma tabular. Pero podemos hacer más (siempre podemos hacer más) que subtotales personalizados y una ruta de Drill-Down cíclico. Podemos mejorar el estilo para reflejar la moderna interfaz de usuario que está tan “de moda” en la comunidad de Tableau, añadiendo una ruta navegable.
2 Como hacerlo
2.1 Modelación de Datos
En una Estado de Resultados, creemos que es crucial personalizar las etiquetas de los subtotales a las convenciones de nomenclatura de una empresa. Para hacer esto dentro de la vista, tenemos que aprovechar la solución de Agregar Sub-Totales Personalizados en Tableau.
Por lo tanto, primero tenemos que modelar los datos:
• Agregue una nueva dimensión a la estructura jerárquica con los nombres de los subtotales en el nivel jerárquico más alto de la cuenta (por ejemplo, Gross Profit, EBIT, etc.)
• Tener una segunda copia de los datos. El uso de dos copias nos permitirá personalizar las etiquetas de subtotal y los cálculos de cantidad para cada copia de los datos de forma independiente.
Jerarquía de cuenta + subtotal de nivel superior
2.2 Definiendo las Jerarquías
En el blog original de Rosario, el menú Drill-Down se maneja independientemente de los gráficos principales. En cambio, en este ejemplo, el Drill-Down se aplica directamente al gráfico principal. Para estos casos, podemos prescindir del último nivel de Drill-Down y utilizar solo 4 niveles de la jerarquía.
Como se describe en la publicación Adding Custom Sub-Totals in Tableau, cada copia manejará un nivel diferente de la jerarquía. Para el nivel de jerarquía más granular, la primera copia de los datos usaremos SUM(), y para la otra copia de los datos, se requerirá la función RUNNING_SUM() para los subtotales.
Finalmente, las fórmulas también se ajustarán para que la acción del parámetro Drill-Down solo se active cuando se selecciona un elemento Copiar 1 de los datos. Seleccionar un nivel de obtención de detalles de Copia 2 no cambiará.
2.3 Acciones de Preparación
PASO 0: MODELACION DE DATOS
Al traer una segunda copia de los datos, el campo “Table Name” se crea automáticamente para ayudarnos a identificar la copia de datos en cuestión.
Aunque es posible utilizar directamente el campo “Table Name” para los cálculos utilizados para identificar la copia de los datos en los que se está trabajando, crearemos una nueva dimensión “Copy” que se utilizará en nuestras fórmulas para identificar con mayor facilidad la copia de los datos con que se esta trabajando.
Copy | CASE [Table Name] WHEN “Sample” THEN 1 WHEN “Sample1” THEN 2 END |
Convierta el campo “Copy” a dimensión con el clic derecho.
PASO 1: CREAR PARAMETROS
Para esta técnica utilizaremos tres parámetros.
PARAMETER | TYPE | CURRENT VALUE | ALLOWABLE VALUES |
Level | Integer | 0 | All |
Path | String | Top Level | All |
Breadcrumb Value | String | 0 | All |
2.4 Definición de los Drill-Down
PASO 2: DIMENSIONES PARA EL DRILL-DOWN
Las dimensiones “DD Level” y “DD Path” se utilizarán para actualizar los parámetros definidos en el primer paso.
El valor de la dimensión ” DD Label ” contendrá la leyenda visible para los usuarios y estará determinada por la copia que se muestra de los datos y el nivel de desglose actual.
- Para la copia 1 de los datos, usaremos Cluster, Position, Type o Account.
- Para la copia 2 de los datos, utilizaremos: Subtotal, Cluster, Position o Type.
“DD Label Group” nos ayudará a agrupar los valores de “DD Label” según el grupo al que pertenecen. Además, ” DD Path (colored)” y ” DD Path (grey)” dividen el parámetro de ruta para diferenciar más claramente entre el nivel actual y los niveles anteriores.
Se necesitará “DD Value” para insertar el valor monetario seleccionado en el parámetro Breadcrumb Value.
DD Level | IIF([Level] = 3, 0,
IIF([Copy] = 1, [Level] + 1, [Level])) |
DD Label Group | CASE [Level]
WHEN 0 THEN [Subtotal] WHEN 1 THEN [Cluster] WHEN 2 THEN [Position] WHEN 3 THEN [Type] END |
DD Label | IIF([Copy] = 2, [DD Label Group],
CASE [Level] WHEN 0 THEN [Cluster] WHEN 1 THEN [Position] WHEN 2 THEN [Type] WHEN 3 THEN STR([Account]) END) |
DD Path | IIF([Level] = 3, “Top Level”, [Path] +
IIF([Copy] = 1, “/” + [DD Label], “”)) |
DD Path (colored) | SPLIT ([Path], “/”, -1) |
DD Path (grey) | IFNULL(LEFT ([Path], LEN ([Path]) – LEN ([DD Path (colored)])),””) |
DD Value | IF [Level] = 3 THEN ‘0’ ELSEIF
MIN([Copy])=2 THEN [Breadcrumb Value] ELSE [Breadcrumb Value] + “/” + STR(ROUND(SUM([CY Ytd])-SUM([PY Ytd]))) END |
Convierta el campo ” DD Level ” en una dimensión.
La siguiente definición es para seleccionar y/o filtrar los registros que cumplan con las selecciones de usuario de Drill Down establecidas en el valor del Path.
DD Filter | [Level] = 0 OR CONTAINS([Path], [DD Label Group]) |
Esta fórmula es una versión simplificada de la utilizada en el blog original de Rosario.
Esto es posible, ya que las descripciones utilizadas en la jerarquía no se repiten ni están contenidas en las descripciones del resto de los valores. Si no se cumplen estas condiciones, se debe utilizar la fórmula original.
2.4 Definiciones para el Drill-Down
PASO 3: CALCULAR LAS DIMENSIONES PARA PRESENTAR LAS OPCIONES DEL MENÚ DRILL UP
Se manejarán cuatro niveles de obtención de detalles: “Net Income”, “Cluster”, “Position” y “Type”. Como no tenemos tales valores en los datos de una dimensión, aplicamos un pequeño truco, que es crear la nueva dimensión desde otro campo, aunque no haya una relación entre la nueva dimensión creada y la dimensión original. En este ejercicio utilizaremos la dimensión Cuenta para crear la nueva dimensión.
DU Level | [Account] % 4 |
Convierta el ” DU Level ” a una dimensión con el clic derecho.
La fórmula descrita aquí calcula el residuo de dividir el valor numérico del campo “Account” por 4, dando como posibles valores números entre 0 y 3. Es decir, estamos agrupando arbitrariamente los datos en cuatro grupos (0, 1, 2, 3), uno para cada una de las opciones de menú de Drill Up requerida
Esta técnica es conocida como “Internal Data Densification”.
PASO 4: CALCULA LAS DIMENSIONES QUE NOS APOYARÁN CON EL DRILL UP
Una vez que la dimensión ” DU Level” se ha definido bajo la técnica de Densificación Interna de Datos, calcularemos el resto de las dimensiones requeridas para el Drill-Up.
Cada una de las nuevas dimensiones estará determinada por: El valor de la dimensión “DU Level ” y por los valores de los parámetros, cuyo valor está determinado por las decisiones tomadas por el usuario.
DU Path | LEFT([Path], FINDNTH([Path] + “/”, “/”, [DU Level] + 1) – 1 |
DU Label | SPLIT([DU Path], “/”, -1) |
Las dimensiones “DU Level” y “DU Path” se utilizarán para actualizar los parámetros definidos en el paso 1. La dimensión “DU Label” contendrá la leyenda que se utilizará en la información sobre herramientas para identificar el nivel de Drill-Up.
Para el gráfico de la forma de la ruta de exploración, podemos identificar el elemento actual de la ruta con “DU Label – Path – Find Last”. El valor actual se inserta en el parámetro Breadcrumb Value con el campo DU Value y se retira del parámetro con el campo “DU Label – Value – Find Last”.
DU Label – Path – Find Last | IF LEN(TRIM(RIGHT([DU Path], LEN([DU Path])
– LEN(REGEXP_REPLACE([DU Path], ‘[^/].$’, ”))))) > 30 THEN LEFT(TRIM(RIGHT([DU Path], LEN([DU Path]) – LEN(REGEXP_REPLACE([DU Path], ‘[^/].$’, ”)))), 30) + ‘…’ ELSE TRIM(RIGHT([DU Path], LEN([DU Path]) – LEN(REGEXP_REPLACE([DU Path], ‘[^/]*.$’, ”)))) END |
DU Value | STR(ROUND([Total variance])) +
IF [DU Level] >= 1 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 2) ELSE ” END + IF [DU Level] >= 2 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 3) ELSE ” END + IF [DU Level] >= 3 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 4) ELSE ” END + IF [DU Level] >= 4 THEN ‘/’ + SPLIT([Breadcrumb Value], ‘/’, 5) ELSE ” END |
DU Label – Value – Find Last | INT(TRIM(RIGHT([DU Value], LEN([DU Value]) – LEN(REGEXP_REPLACE([DU Value], ‘[^/]*.$’, ”))))) |
Para finalizar las definiciones de Drill Up, se requiere un filtro para seleccionar los niveles del menú Drill Up que serán visibles. “DU Color” nos permitirá distinguir el último nivel utilizado, de los niveles superiores en la jerarquía.
DU Filter | [DU Level] <= [Level] |
DU Color | [DU Level] = [Level] |
2.6 CALCULAR MONTOS
PASO 5: CALCULAR LOS VALORES DEL AÑO ACTUAL, AÑO ANTERIOR Y LA DIFERENCIA ENTRE AMBAS CANTIDADES
Para definir las fórmulas, debemos tener en cuenta la copia de los datos con los que estamos trabajando. Y tenga cuidado de no duplicar las cantidades de las dos copias de los datos.
Se utilizará una línea de referencia para traer espacio entre el encabezado y lo visual en nuestra vista tabular.
CY YTD | CASE MIN([Copy])
WHEN 1 THEN SUM([CY Ytd]) WHEN 2 THEN RUNNING_SUM(SUM([CY Ytd])) / 2 END |
PY YTD | CASE MIN([Copy])
WHEN 1 THEN SUM([PY Ytd]) WHEN 2 THEN RUNNING_SUM(SUM([PY Ytd])) / 2 END |
Bridge | [CY YTD] – [PY YTD] |
Bridge Axis | CASE MIN([Copy])
WHEN 1 THEN RUNNING_SUM([Bridge]) WHEN 2 THEN [Bridge] END |
Color Waterfall | IIF(MIN([Copy]) = 2, “grey”,
IIF([Bridge] < 0, “red”, “blue”)) |
Reference Line | WINDOW_MIN([Bridge]) * 1.5 |
2.7 CREAR LAS VISTAS
PASO 6: CREA UN GRÁFICO DE GANTOS COMO EL GRÁFICO PRINCIPAL
Filtrar:
- Filter ⬅ DD_Filer = TRUE
Mover a los estantes de:
- Columns ⬅ Nuevo Cálculo “BRIDGE” y Bridge_Axis
- Rows ⬅ “DD Label Group” ordenado por el campo “No” y se oculta header
- Rows ⬅ “Copy” se oculta header
- Rows ⬅ “DD Label” ordenado por el campo “No”
- Rows ⬅ “PY YTD” y “CY YTD”
Para los estantes de:
- Color ⬅ Color Waterfall
- Size ⬅ -Bridge
- Detail ⬅ DD Level, DD Path, Reference Line
- Tooltips ⬅ DD Path (Colored) y DD Path (gray)
- Text ⬅ Bridge
Para los cálculos de la tabla, ajuste su cálculo a dimensiones específicas: DD Label Group, Copy, DD Label, DD Level y DD Path
Ajustar los colores de las barras de la cascada.
Editar el texto de la hoja de trabajo para mostrar los campos <<ATTR (DD Path (gray))> <ATTR (DD Path (colored))> y modifique los colores que se utilizarán en cada campo.
Establecer bandas de renglón en el nivel de copia. Esto permite que el usuario del panel identifique realmente el subtotal en la vista principal.
PASO 7. CREAR EL GRÁFICO DE FORMA PARA EL DRILL-UP.
Filtrar:
- Filter ⬅ DU_Filer = TRUE
Mover a los estantes de:
- Columns ⬅ DU Label y hacerla continua
- Detail ⬅ DU Path
- Detail ⬅ DU Value
- Color ⬅ DU Color
- Tooltips ⬅ DD Label
- Text ⬅ DU Label – Path – Find Last
- Text ⬅ DU Label – Value – Find Last
- Text ⬅ Variance in %
Use la marca de forma y seleccione una forma de flecha.
2.8 CREAR EL DASHBOARD Y DEFINIR ACCIONES DE PARÁMETRO
PASO 9: ACCIONES DE PARAMETROS
Después de juntar los gráficos en el dashboard, se definirán las siguientes acciones de parámetros para:
Drill-Down:
PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
DD Level | Chart | Level | DD Level | Average |
DD Path | Chart | Path | DD Path | None |
DD Value | Chart | Breadcrumb Value | DD Value | None |
Drill-Up:
PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
DU Level | Top Menu | Level | DU Level | Average |
DU Path | Top Menu | Path | DU Path | None |
DU Value | Breadcrumb Value | Breadcrumb Value | DU Value | None |
3 ADAPTELO A SUS REQUERIMIENTOS
Armado con el conocimiento compartido hasta este punto, se pueden crear jerarquías completamente navegables dentro de cualquier contexto relevante, no solo financiero. Creemos que esto va a ser un cambio radical en el diseño y la funcionalidad del tablero proveniente de nuestra querida comunidad de Tableau.
Este enfoque podría reemplazar los filtros de solo valores relevantes, aumentando el rendimiento. Mantenga a su audiencia enfocada solo en la información más significativa, aumentando en gran medida la perspicacia de sus dashbaords.
A propósito, hemos mantenido el estilo simple, pero para aquellos creativamente curiosos entre ustedes, asegúrese de echar un vistazo a mi Breadcrumb Collection en Dribbble. ¡No puedo esperar para ver lo que todos ustedes crean!
¡Eso es! Esperamos que haya disfrutado leyendo y encuentre casos de uso propios para esto. Encuentre y descargue el libro de trabajo en Tableau Public aquí.
Si tiene alguna pregunta, comuníquese con nosotros en Twitter (@ProfDrKSchulte, @rosariogaunag y @robcrock).