En blogs previos habíamos ya trabajado con Drill Down y Drill Up utilizando Acciones de Parámetros. En aquellos ejercicios, la definición del menú para el Drill Up se lograba utilizando varios sheet para el menú, uno por cada opción de Drill Up requerida.
En este blog les compartiré una nueva técnica para presentar las opciones del Drill Up utilizando un solo sheet.
¿Para que utilizamos el Drill Down y el Drill Up?
El Drill Down es una herramienta muy útil para las consultas ya que permite al usuario ir de lo general a lo específico. Esto es, cuando utilizamos un menú Drill Down navegamos en una jerarquía de dimensiones o niveles desde la dimensión superior a la inferior, al ir seleccionando uno de los valores de la dimensión presentada.
En cuanto al Drill Up, implica ir de lo específico a lo general: Esto es, al utilizar un Menú Drill Up navegamos hacía dimensiones superiores de la jerarquía con la diferencia de que los saltos en los niveles pueden ser abruptos, es decir podemos seleccionar cualquiera dimensión de las desplegadas.
El Drill Down y el Drill Up tiene múltiples usos, por ejemplo, para la consulta de informes contables, financieros, de inventarios, cartera, etc., ya que permite a partir de una información visualizada, “sumergirse o bucear ” en esa información con el fin de conocer el detalle de los datos que han servido para crearla.
En mi caso, en donde he trabajado con información financiera por bastante tiempo, he visto que cuando una cifra sale de su rango normal, o por una lado, se presenta la necesidad de entender que fue lo que originó esa desviación; o por otro lado, la persona desconfía de los números, por lo cual, el poder utilizar una herramienta que ayuda en la trazabilidad, es decir, saber de donde viene la información, brinda un mayor entendimiento, credibilidad y confianza al momento de analizar y presentar información.
Requerimientos:
- Utilizar Orders de superstore dataset de Tableau 2019.4.
- Las dimensiones Región, Estado, Ciudad y Código Postal de Superstore serán utilizadas para la navegación de Drill Down y Drill Up.
- En el lado izquierdo, se tendrá el menú en un solo sheet del Drill Down, en donde se desplegará los valores del siguiente nivel de consulta dentro de la jerarquía, de entre los cuales se podrá seleccionar una de las opciones.
- En la parte superior, se tendrá un menú en un solo sheet, en donde se desplegarán de uno hasta cinco niveles de Drill Up. Este menú nos ayudará: 1) Por un lado, como encabezado para indicar los valores de la consulta vigente en las gráficas centrales; y 2) Para permitir que el usuario pueda seleccionar cualquiera de las opciones de los niveles superiores de la jerarquía, para regresar la consulta directamente al nivel deseado.
- En la parte central se ubicarán la(s) gráfica(s) de consulta.
A continuación, comparto los pasos que seguí para crear los menús.
PASO 1. CREAR PARAMETROS.
Para este ejercicio requeriremos únicamente de dos parámetros. Estos parámetros son compartidos y actualizados por ambos menús.
| PARAMETER | TYPE | CURRENT VALUE | ALLOWABLE VALUES |
| Level | Integer | 0 | All |
| Path | String | Overall | All |
Para el valor inicial del “Path” utilicé la descripción de “Overall” para indicar el punto inicial en el Drill Down de la jerarquía, sin embargo, puede utilizarse cualquier otra descripción, como podría ser: “Home”, “Total”, “Estados Unidos”, etc.
En caso de que usted decida utilizar otra descripción, solo deberá sustituir en las formulas el string de “Overall”, con la nueva descripción.
DEFINICIONES PARA EL DRILL DOWN
PASO 2. CALCULAR LAS DIMENSIONES QUE NOS APOYARAN CON EL DRILL DOWN.
Se manejarán 5 niveles de Drill Down: Región, Estado, Ciudad, Código Postal y Return to Overall. Es decir, un nivel para cada dimensión incluida en el Drill Down, más un nivel adicional para presentar la opción de cerrar el ciclo actual y reinicializar con uno nuevo.
El campo de Código Postal por default es considerado como numérico, por lo cuál, es requerido cambiar su definición para que sea considerado como string y así concordar con el tipo de datos manejado en el resto de los valores de la jerarquía.
Las dimensiones “DD Level” y “DD Path” nos servirán para la actualización de los parámetros definidos en el paso 1 y la dimensión “DD Label” tendrá el valor de la leyenda visible del menú para que el usuario identifique la opción del Drill Down deseada.
| DD Level | IIF([Level] = 4, 0, [Level] + 1) |
| DD Label | CASE [Level] WHEN 0 THEN [Region] WHEN 1 THEN [State] WHEN 2 THEN [City] WHEN 3 THEN [Postal Code] WHEN 4 THEN “Overall” END |
| DD Path | IIF([Level] = 4, “Overall”, [Path] + “/” + [DD Label]) |
Convertir el campo “DD Level” a dimensión con el click derecho.
Yo utilicé para el último nivel de “DD Label”, la descripción de “Overall”, sin embargo, puede ser sustituido por el valor del último campo de la jerarquía dependiendo de, cuál opción se acomode mejor a lo que esta presentando.
La siguiente definición es para seleccionar y/o filtrar de Superstore los registros que cumplan con las selecciones del usuario de Drill Down establecidas en el valor del Path.
Actualización: 6/Junio/2020
Fórmula compacta:
| DD Filter | CONTAINS(“Overall/” + [Region] + “/” + [State] + “/” + [City] + “/” + [Postal Code], [Path]) |
Fórmula original:
| DD Filter | ([Region] = SPLIT([Path], “/”, 2) OR SPLIT([Path], “/”, 2) = “”) AND ([State] = SPLIT([Path], “/”, 3) OR SPLIT([Path], “/”, 3) = “”) AND ([City] = SPLIT([Path], “/”, 4) OR SPLIT([Path], “/”, 4) = “”) AND ([Postal Code] = SPLIT([Path], “/”, 5) OR SPLIT([Path], “/”, 5) = “”) |
Fin de la actualización
Por último, calcularemos la leyenda del título y del subtítulo para el encabezado del menú del Drill Down.
| DD Title | IIF([Level] < 4, “Drill Down”, ” “) |
| DD Subtitle | CASE [Level] WHEN 0 THEN “by Region” WHEN 1 THEN “by State” WHEN 2 THEN “by City” WHEN 3 THEN “by Zip Code” WHEN 4 THEN “Return to” END |
PASO 3. CREAR EL SHEET PARA EL MENU DEL DRILL DOWN.
Mover a los estantes de:
- Filters ⬅ Year of Order Date = 2019
- Filters ⬅ “DD Filter” = TRUE
- Rows ⬅ “DD Level” y “DD Path”
- Text ⬅ “DD Label”
- Tooltips ⬅ “DD Title” y “DD Subtitle”
Los campos de “DD Level” y “DD Path” se deben ocultar, para lo cual, se debe seleccionar el campo con el click derecho y deseleccionar la opción de “Show Header”.
Los campos “DD Title” y “DD Subtitle” se utilizarán para definir el título del sheet.
Ajustar colores y formatos.
DEFINICIONES PARA EL DRILL UP
PASO 4. CALCULAR LA DIMENSION PARA PRESENTAR LAS OPCIONES DEL MENU DEL DRILL UP.
Se manejarán hasta 5 niveles de Drill Up: Overall, Región, Estado, Ciudad y Código Postal. Es decir, un nivel por cada nivel de Drill Down que se ha navegado en la jerarquía de dimensiones, más un nivel adicional con la opción regresar a la consulta total “Overall”.
Como no contamos en Superstore de una dimensión con tales valores, aplicamos un pequeño truco, el cual consiste en crear la nueva dimensión a partir de otro campo, aunque no exista una relación entre la nueva dimensión creada y la dimensión original. En este ejercicio utilizaremos la dimensión Order Date para crear la nueva dimensión.
| DU Level | INT([Order Date]) % 5 |
Convertir “DU Level” a dimensión con el click derecho.
La fórmula aquí descrita calcula el residuo de dividir el valor numérico de la fecha del campo “Order Date” entre 5, dando como posibles valores números entre 0 y 4. Es decir, estamos agrupando arbitrariamente los días en cinco grupos (0, 1, 2, 3, 4), uno por cada valor requerido para la nueva dimensión u opciones del menú requeridas.
Actualización: 13/ July/2020
Si entre sus datos no cuentan con un campo de fecha, no se preocupe, ya que es posible utilizar cualquier otra dimensión, inclusive cualquiera de las dimensiones de la jerarquía con la que va a trabajar, el único requisito es que la dimensión original tenga al menos tantos valores diferentes, como lo que se son requeridos para el menú del Drill Up (en este caso, al menos 5 valores diferentes). También puede usar otro tipo de formula para crear la nueva dimensión, por ejemplo, la función Case() les podría ser de utilidad.
Fin de la actualización
Esta técnica se conoce como “Densificación Interna de Datos” y como ya lo habíamos visto en el post de inicio de año: “Como ordenar Asc/Desc por una Dimensión o Medida con Acciones de Parámetros” es una herramienta muy útil para la definición de menús en donde las opciones no están definidas en la fuente de datos.
A diferencia del ejercicio realizado en el blog antes mencionado, en donde las opciones y las leyendas del menú eran fijos, ya que cada opción del menú tenía asignada un único nombre de medidas y/o dimensión; en este nuevo ejercicio, la definición del menú es bastante dinámica, ya que tanto la cantidad de opciones a ser presentadas, como las leyendas han ser desplegadas, variarán y dependerán de las selecciones previas del usuario.
PASO 5. CALCULAR DEL RESTO DE LAS DIMENSIONES QUE NOS APOYARAN CON EL DRILL UP.
Una vez definida la dimensión “DU Level” 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án determinado por las decisiones tomadas por el usuario.
Actualización: 6/Junio/2020
Fórmula compacta:
| DU Path | LEFT([Path], FINDNTH([Path] + “/”, “/”, [DU Level] + 1) – 1) |
Fórmula original:
| DU Path | “Overall” + IIF([DU Level] >= 1, “/” + SPLIT([Path], “/”, 2), “”) + IIF([DU Level] >= 2, “/” + SPLIT([Path], “/”, 3), “”) + IIF([DU Level] >= 3, “/” + SPLIT([Path], “/”, 4), “”) + IIF([DU Level] >= 4, “/” + SPLIT([Path], “/”, 5), “”) |
Fin de la actualización
Actualización: 29/Junio/2020
Fórmula compacta:
| DU Label | IIF([DU Level] = 0, “”, “/”) + SPLIT([DU Path], “/”, -1) |
Fórmula original:
| DU Label | CASE [DU Level] WHEN 0 THEN “Overall” WHEN 1 THEN “/” + SPLIT([Path], “/”, 2) WHEN 2 THEN “/” + SPLIT([Path], “/”, 3) WHEN 3 THEN “/” + SPLIT([Path], “/”, 4) WHEN 4 THEN “/” + SPLIT([Path], “/”, 5) END |
Fin de la actualización
Las dimensiones “DU Level” y “DU Path” serán utilizadas para actualizar los parámetros definidos en el paso 1. La dimensión “DU Label” contendrá la leyenda visible del menú para que el usuario seleccione la opción del Drill Up deseada.
Para terminar las definiciones para el Drill Up, se requiere de un filtro para seleccionar los niveles del menú de Drill Up que estarán visibles y de la definición del color de las opciones del menú que nos permitirán distinguir el último nivel utilizado, de los niveles superiores en la jerarquía.
| DU Filter | [DU Level] <= [Level] |
| DU Color | [DU Level] = [Level] |
PASO 6. CREAR EL SHEET PARA EL MENU SUPERIOR DEL DRILL UP.
Mover a los estantes de:
- Filters ⬅ Year of Order Date = 2019
- Filters ⬅ “DU Filter” = TRUE
- Columns ⬅ “DU Level” y “DU Path”
- Text ⬅ “DU Label”
- Color ⬅ “DU Color”
Los campos de “DU Level” y “DU Path” se deben ocultar, para lo cual, se debe seleccionar el campo con el click derecho y deseleccionar la opción de “Show Header”.
En un inicio, el menú de Drill Up solo mostrará la primera opción del menú “Overall”, el resto de las opciones irán apareciendo una a una, conforme se vaya aplicando el Drill Down sobre la jerarquía al utilizar las Acciones de Parámetro.
Ajustar colores y formatos.
DEFINICION DE LA(S) GRAFICA(S) DE CONSULTA PRINCIPAL
PASO 7. DEFINICIÓN LA(S) GRAFICA(S) DE CONSULTA PRINCIPAL.
Para las gráficas principales de consulta en este ejercicio, yo utilicé un conjunto de gráficas incluidas en uno de los grandiosos templates que recientemente nos compartió Andy Kriebel y así aproveché por un lado el trabajo realizado en su diseño y, por otro lado, aproveché los cálculos realizados en los comparativos anuales. El template puede ser consultado aquí.
Los cuatro sheets de KPI definidos en el template nos permiten la consulta mensual de las ventas, de la utilidad, de la cantidad de órdenes y de los clientes del año 2019. Estos 4 sheet son los que incorporé en el diseño final de mi ejercicio.
Pero también es perfectamente válido, el que usted defina su(s) propia(s) gráfica(s) de consulta principal. Pueden ser tan sencillas como el solo mostrar los montos totales del 2019 de una o más variables o pueden ser tan elaborada como usted lo desee para poner en práctica esta técnica.
Para sincronizar el nivel de Drill Down y Drill Up con las gráficas de consulta principales, se debe incluir el filtro de “DD Filter” = TRUE
Si usted, al igual que yo, utilizó los 4 gráficos de KPI del template, requiere definir el filtro como de contexto, para que los cálculos de los gráficos de KPI sean correctamente realizados.
En cambio, si usted definió su propio gráfico, asegúrese de que además de utilizar el filtro “DD Filter”, también utilizar el filtro que corresponde a los años de consulta.
CREAR EL DASHBOARD
PASO 8. Se crea un dashboard que integre el sheet del menú para el Drill Down, el sheet del menú para el Drill Up y las gráficas de consulta.
DEFINICIÓN DE LAS ACCIONES DE PARAMETRO
PASO 9. DEFINICION DE ACCIONES DE PARAMETROS.
Para el dashboard creado en el paso previo, se definirán las siguientes Acciones de Parámetro para :
El Drill Down del menú izquierdo.
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| DD Level | Left Menu | Level | DD Level | Average |
| DD Path | Left Menu | Path | DD Path | None |
El Drill Up del menú superior.
| 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 |
Para entender más el cómo funcionan los menús de Drill Down y Drill Up, a continuación, comparto una imagen con el valor de las tres variables principales involucradas, cuando la última selección del usuario fue la consulta del Estado de New York.
En la imagen, también se muestra como las definiciones de ambos menús parten de distintas fuentes.
¡Listo!
La versión interactiva se puede consultar aquí.
PARA CONCLUIR
En este blog trabajamos con una nueva técnica que nos permite manejar Drill Down y Drill Up con un menor número de parámetros y que las opciones del Drill Up pueden ser mostradas en un solo sheet.
Para la definición de las opciones del menú del Drill Up, utilizamos a su vez, la técnica de Densificación Interna de Datos, la cual, como ya lo hemos visto en ejemplos previos, la considero ideal para la definición de menús en donde las opciones no están definidas en la fuente de datos.
En este ejercicio, pudimos ejemplificar como la definición del menú con la técnica Densificación Interna de Datos puede ser tan dinámica como sea requerido, ya que la cantidad de opciones a ser presentadas, así como las leyendas que han de ser utilizadas en el menú no son estáticas, ya que son resultado de las selecciones previas del usuario.
El procedimiento utilizado en el presente ejercicio también puede ser utilizado para gráficas en donde el Drill Down es aplicado directamente en la gráfica principal, para lo cual, solo será requerido pequeños ajustes en algunas de las fórmulas. Puede consultar los siguientes dos ejemplos:
Un ajuste en sus formulas de estos ejemplos, fue que prescindí del último nivel del Drill-Down, ya que al estar inmerso el Drill Down en la gráfica principal, el último nivel no ofrecía información adicional, a menos que la gráfica este relacionada con otras gráficas en el dashboard, que no fue el caso de estos ejemplos.
Deseo agradecer a Andy Kriebel por los grandiosos templates que recientemente nos compartió, ya que al utilizarlos facilitan nuestro trabajo al aprovechar el diseño y la definición de los cálculos necesarios para la construcción de las gráficas principales.
También deseo agradecer su feedback en el camino a Ann Jackson, Klaus Schulte y Luke Stanke.
Muchas gracias y si tiene alguna pregunta sobre el post, no dudes en ponerte en contacto conmigo en Twitter (@rosariogaunag).
RosarioViz 