Construcción De Tablas De Calendario y Porqué Son La Bomba
Al parecer se ha engendrado toda una leyenda con toques místicos respecto a las tablas de calendario y es que, siempre que tenemos que trabajar con fechas con Power Pivot no dudamos en sacar una de «esas» del bolsillo y utilizarla a nuestro favor.
… Y puedo casi escucharte:
«Pero Miguel, siempre que tratan con tablas de calendario en algún artículo o vídeo lo expresan de manera sucinta, claro a excepción del más reciente post que enseña cómo construir una tabla de calendario en la venta de Power Pivot»
*** Y lo sé …
Pues ninguna de las sesiones disponibles de la series aplicaciones en Power Pivot se salva de trabajar con una tabla de calendario (a excepción de 2 y 3)
.
– Por eso en este artículo quiero hacer varias cosas:
1) Definir qué es una tabla de calendario o tabla de fechas
2) Características y propiedades de una tabla de calendario
3) Métodos de construcción de tablas de calendario
4) Cómo interactúa la tabla de calendario con las demás tablas
5) Tener claro si es o no obligatorio una tabla de calendario para trabajar con power pivot
6) Cuáles son los beneficios de las tablas de calendario y porqué son la bomba
7) Extensión a necesidad de una tabla de calendario
8) Tabla de calendario personalizado
9) Generador de tabla de calendario estándar
.
¿Quieres conocer los detalles y respuestas de estos ítems?
Entonces, ponte un casco y no te separes de la pantalla.
.
PUNTO I: Definción De Tabla De Calendario
Antes de dar una definición para tabla de calendario (Por cierto, también son conocidas como tablas de fechas) recordemos qué es una tabla de búsqueda.
Una tabla de búsqueda es aquella que contiene campos que son utilizados para restringir y agrupar registros en tablas transaccionales.
Sí, sé que eso se leyó como si se tratará del lenguaje alíen del film arrival (Complejo y rozando los límites de lo incomprensible)
Permíteme probar otra aproximación:
¿Está familiarizado con BUSCARV?
Mejor que mejor
Porque una tabla de búsqueda sería aquella que ponemos en el argumento matriz_buscar_en mientras que la tabla donde estamos creando la función BUSCARV sería la tabla transaccional.
Para ponerlo en términos coloquiales para lo que necesitamos:
Definición Tabla de Búsqueda: Es una tabla en la cual por lo menos uno de sus campos tiene elementos que no se repite (cada ítem o elementos es único identificando de forma univoca cada registro de la tabla).
– Ejemplo:
Si aún tienes dudas te recomiendo la sesión en vídeo número 1 de la serie aplicaciones en Power Pivot allí se explican dichos conceptos así como relacionar tablas con Power Pivot.
.
*** Ahora sí …
Definición Tabla de Calendario: Una tabla de calendario es un tipo especial de tabla de búsqueda que tiene como finalidad poder categorizar elementos en el campo fecha en diversos grupos de acuerdo a parámetros en esa fecha.
.
Desglosemos la definición por partes para tratar de dejarlo lo más claro posibles
Esta columna en la tabla de calendario es una que contiene todas las fechas de manera consecutiva entre dos fechas extremas
.
– Ejemplo: si la tabla de calendario va de 1/1/2016 al 31/12/2016, entonces una y cada una de las fechas entre esta dos últimas debe estar una única vez
Nótese como van apareciendo de manera consecutiva y una única vez las diversas fechas sin excepción (la imagen anterior muestra un fragmento de la columna, la tabla original llega hasta el 31/12/2016)
¡Importantísimo!: A esa columna que contiene las fechas consecutivas de manera única generalmente se le denomina: Fechas ó Fechas (En inglés la llaman también Date o DateKey). La definición dice: «Categorizar elementos por el campo fecha» Ese campo es precisamente el campo con elementos únicos (No repetidos)
También la definición indica que es: «especial». Ello es así porque está pensado para el trabajo con fechas y habilita un conjunto de funciones especiales llamadas time intelligence.
.
La definición continua con:
– Ejemplo: De la fecha (columna con fecha consecutiva) podemos extraer a que mes pertenece esa fecha
De la tabla y las columnas creadas se aprecia claramente que el campo Mes Nombre le ha asignado a todas las fechas del primer mes del año la etiqueta enero, para el segundo mes febrero y así sucesivamente.
Como intuimos directamente dicha columna (Mes Nombre) la podemos utilizar posteriormente en un reporte de tabla dinámica para ver los resúmenes de los datos en los disntintos meses.
Otro par de columnas que se ilustran en la imagen, ellas son:
.
– Mes Número: En lugar de asignar el nombre (enero) asigna el número de mes 1, y así para cada mes (Columna especialmente últil para ordenar)
– Trimestre: Otra categoría que nos permite agrupar las fechas con otra caracteristica, en este caso a que trimestre del año pertenece
.
«!» Note que cada una de estas agrupaciones va en su respectiva columna
.
¿Cuáles son las columnas que debe tener una tabla de calendario?
.
Aquí no hay una regla de oro, dado que las columnas las creas a tu medida y necesidad, pero yo te recomiendo como mínimo las siguientes
Llamémoslas las Columnas Primordiales para tabla de calendario:
.
– Fecha: Ok. Esta si es obligatoria. Es la columna con valores únicos que hemos venido discutiendo
– Año: Si tus datos pasan la frontera de un año, entonces es indispensable añadir esta categoría para los años
– Mes Número: Columna que indica si la fecha pertenece al primer mes del año, al segundo, tercero, etc. Mediante un número entero entre el 1 al 12
– Mes Nombre: Indica a que mes pertenecela fecha en la fila (enero, febrero, marzo, …)
– Trimestre: Indica a que trimestre del año pertenece la fecha actual
– Día de la Semana Nombre: Informa a cuál de los días de la semana pertenece la fecha en la fila (Lunes, Martes, Miércoles,…)
– Día de la Semana Número: Columna que indica si la fecha pertenece al primer día de la semana, al segundo, tercero, etc. Mediante un número entero que va del 1 al 7 (También útil para ordenar)
.
¿Son esas columnas las únicas para una tabla de calendario?
.
No. Creamos las columnas a nuestra necesidad
Sí necesitas un columna que señale los bimestres, cuatrimestres, semestre lo puedes hacer
O incluso
Agrupar períodos más extenso como en lustros, decadas, etc. También es válido
En resumen cualquier sistema de tiempo que necesites para segmentar tus datos en el reporte de tabla dinámica
Lo clave es que dichas categorías se basen en la columna fecha
Si ello es así todo esta en orden
Discutamos ahora unas propiedades …
PUNTO II: Características y propiedades de una tabla de calendario
Ya entendemos y conocemos qué es una tabla de calendario
Igual de importante es saber esas caraterísticas y propiedades que debe tener para que sea una tabla de calendario con todas las de la ley
.
.
Conclusión: la tabla de calendario (estándar), cumple con una y cada una de las condiciones del calendario gregoriano o ese calendario que tenemos «colgado en la pared» o el del celular
¡Importante!: resaltar que las tablas de calendario de las cuales estamos hablando son tablas de calendario estándar.
Me voy a robar con mucho «sigilo» una imagen del libro El ADN de Power Pivot, la ilustración 10.2 del capítulo 10 que en una sola vista presenta las propiedades de una tabla de calendario estándar.
PUNTO III: Métodos De Construcción De Una Tabla De Calendario Estándar
So far, so good ..
Quizá ya hayas esbozado en tu mente una o varias formas de poder crear una tabla de calendario estándar
Pues comprendiendo qué es y cuáles son propiedades no es difícil poner sobre la mesa métodos para su construcción
Listemos algunas alternativas para construir una tabla de calendario:
.
.
Enfilar un método ya depende de nuestra necesidad específica y elección personal.
Una parte nada desdeñable de la lista para la construcción de una tabla de calendario estándar se trata bien sea en El ADN de Power Pivot o en el Tomo #3 del Master en DAX y Power Pivot, incluso en el blog (aunque aconsejable sólo para soluciones rápidas) puedes encontrar la construcción desde la interfaz de Power Pivot semi-automática
.
PUNTO IV: Cómo Interactúa La Tabla De Calendario Con Las Demás Tablas
La tabla de calendario se debe relacionar con las tablas transaccionales en el modelo de datos, sabiendo que una tabla de calendario es una tabla de búsqueda no hay mayor misterio en ese punto.
Supongamos, por ejemplo, que tenemos una tabla transaccional con los registros de ventas día a día de los diversos productos de la compañía denominada Sells, tenemos una tabla de búsqueda «normal» que indica información adicional de los campos llamada ProductDatils mas la tabla de calendario (Calendario), el entretejido de tablas sería:
(El entretejido de tablas de la imagen anterior es un creenshot con Power Pivot 2013).
.
Si tienes inquietudes respecto esto no dejes de ver el vídeo: Crear Tabla Dinámica con Power Pivot (Relacionar Múltiples Tablas)
En última instancia una tabla de calendario es uan tabla de búsqeuda, con lo cuál los filtros de dicha tabla se porpagan a la tabla o tablas transaccionales.
.
«!» Gracias a las tablas de calendario y su forma de interactuar con las demás podemos orquestar todo tipo segmentaciones y análisis plausibles
.
Por cierto, en un mismo modelo de datos podemos tener más de una tabla de calendario cada uno con un proposito, de momento se sale del ámbito del presente artículo, no obstante, es una opción que es válido y crucial para ciertos modelos de datos.
PUNTO V: Tener Claro Si Es o No Obligatorio Una Tabla De Calendario
He aquí un punto a enmarcar: No es obligatorio utilizar tablas de calendario, no es camisa de fuerza acudir a ellas en nuestros modelos de datos.
A pesar de ello tildar a las tablas de calendario como opcionales sería osado
La cuestión es que brindan son tan versátiles, llena de posibilidades y formidables que es una buena práctica utilizarla siempre.
.
¿Por qué son opcionales?
.
Sigamos el ejemplo del modelo de datos anterior que presente las tres tablas en el entretejido en Excel 2013, pero en esta ocasión omitamos que tenemos la tabla de calendario, con lo cual el entretejido queda:
Nos plantean que construyamos una tabla dinámica utilizando el modelo de datos para que muestre la suma de las unidades vendidas en el año 2016 para cada uno de los meses.
*** Primero lo primero …
Como es con el modelo de datos
Aquí en EFB somos de la escuela: Medidas explicitás siempre, SIEMPRE !! si no sabes porqué no dejes de ver la sesión de Nociones en Power Pivot
Así que primero una medida sencillísima para la suma de las unidades
Ahora llevamos la medida recién creada, Unidades al área de valores y el campo Date al área de filas:
Para terminar de cumplir con los que nos demandan solo nos hace faltar agrupar las fechas por meses, para ello pulamos clic encima en alguna de fecha de la tabla dinámica y vamos a la opción agrupar en el menú contextual que se despliega
Pero oh sorpresa !!!!!!!!!!!!!!!!!!!!!!
La opción se pronuncia ante nosotros como deshabilitada
.
Un nuevo problema surge a la luz: ¿Cómo agrupar en tablas dinámicas de Power Pivot? este «percance» ya lo hemos tratado aquí en EFB
Maravilloso, eso es en Excel 2013 …
¿Cunetas con Excel 2016? las cosas se facilitan, dado que una de las novedades en tablas dinámicas para Excel 2016 es la agrupación automática en Fecha y tiempo
Por lo anterior cuando llevamos el campo Date al área de valores utilizando Excel 2016 el resultado es el siguiente:
Y problema resuelto
Pues bien, no demasiado…
.
¿Qué pasa en Excel 2013?
.
…Y digamos que en lugar de agrupados por meses desean el reporte de tabla dinámica agrupados por Semestres o Teimestres
.
¿Qué hacer? → Una columna en la tabla de calendario con la categoría, en este caso Trimestre y/o Semestres
.
El punto es que podemos trabajar con fechas sin necesidad de tablas de calendario, pero con limitantes, por lo que es una buena práctica el uso de tabla de calendario en la medida de lo posible.
Sigamos estos dos lineamientos:
.
PUNTO VI: Cuáles Son Los Beneficios De Las Tabla De Calendario y Porqué Son La Bomba
Un modelo de datos sin una tabla de calendario es como un carro sin motor o una bicicleta sin ruedas.
¡Que melodramático! No tanto en realidad
Te menciono algunos beneficios de utilizar tablas de calendario
.
.
PUNTO VII: Extensión A Necesidad De Una Tabla De Calendario
Al final del día hemos visto que utilizamos los campos de la tabla de calendario para segmentar en las tablas transaccionales, no obstante, no todas las columnas en una tabla de calendario tienen dicho fin.
En una tabla de calendario tenemos dos tipos de columnas o campos:
.
.
– Columnas de Segmentación
Las más sencillas de entender son las columnas de segmentación, debido a que simplemente son aquellos campos que utilizamos para agrupar los datos, y añadir a algunas de las áreas de colocación a necesidad, ejemplo: Año, Mes, Trimestre, etc.
Las columnas de segmentación son visibles para el usuario, pues como se mencioné hace una nimiedad dichas columnas (campo) son lo que arrastramos a algunas de las área de colocación para crear el reporte de tabla dinámica
– Columnas Técnicas
Las columnas técnicas pueden ser pensadas como columnas auxiliares y cuyo objetivo primo es identificar y/o manipular partes o subconjuntos de la tabla de calendario para así llevar a buen término un cálculo (que implican fechas) deseado.
Las columnas técnicas también son vitales para el funcionamiento del modelo de datos
Por ejemplo:
Para crear medidas con DAX en algunos casosa es necesario identificar cada combinación (Año, Mes) de manera única indepediente de una fecha en singular, en concreto, si tenemos el año 2016 y mes enero asginarle a todas las fechas que correspondan a esa combinación (Año, Mes) un valor que identifique esa combinación
Puede ser el número 1 como muestra la imagen a continuación (Véase la columna AñoMesNúmero)
.
.
Ciertamente el valor 1 se repite, pero si somos meticulosos veremos que ese uno únicamente se asigna si y solo si la el mes enero y año 2016, en caso de que sea un mes diferente digamos combinación 2016 febrero el indicador debe cambiar.
Sigamos el ejemplo para tratar de no dejar ningún tipo de duda:
.
• Cualquier fecha para Año: 2016, Mes: Enero | Un valor único que identifique la combinación, ejemplo → 1
• Cualquier fecha para Año: 2016, Mes: Febrero| Un valor único que identifique la combinación, ejemplo → 2
• Cualquier fecha para Año: 2017, Mes: Enero | Un valor único que identifique la combinación, ejemplo → 13
• Cualquier fecha para Año: 2017, Mes: Febrero| Un valor único que identifique la combinación, ejemplo → 14
.
El campo AñoMesNúmero sirve para hacer cálculos internos y crear medidas más no para utilizarla en un área de colocación y ver análisis a partir de ella directamente
Un ejemplo de cómo utilizar la columna AñoMesNúmero es para la creación de la medida Pronóstico Media Móvil Simple, en la serie aplicaciones en Power Pivot puedes encontrar el ejemplo.
*** Aquí quiero hacer un pequeño paréntesis de una posibilidad para construir la columna AñoMesNúmero
No te preocupes no me voy ir por las ramas
.
Te dejo la fórmula de Excel para copiar y pegar. Ten en cuenta que la tabla de calendario debe tener los mismos nombres en sus campos y en nombre de tabla, de lo contrario debes reajustar los nombres en la fórmula antes de copiar y pegar.
.
Antes de terminar con columnas técnicas, es bueno señalar que es una buena práctica ocultar dichas columnas del panel de campos
¿Cómo lo hacemos?
En el minuto 5:50 de la sesión en vídeo mejorar reportes de tablas dinámicas de la serie nociones en Power Pivot se muestra precisamente como hacerlo, francamente si no has visto al serie nociones en Power Pivot te la recomiendo al 100%
PUNTO VIII: Tablas De Calendarios Personalizadas
Inexorablemente el artículo hasta aquí aplica en exclusiva para tablas de calendario estándar, es decir, tablas de calendario que cumplen con todas las propiedades del PUNTO II, pero hay que ser claros las tablas de calendario estándar no son las únicas, existen la personalizadas.
Un ejemplo sencillo de un calendario personalizado es uno académico tipo universidad
Traigo a colación el calendario académico de la Escuela Colombiana de Ingeniería (Bogotá, Colombia) para el año 2017
Surfeando por encima del calendario, nos damos cuenta que:
.
• No todos los días son válidos (vacaciones y festivos no son académicos)
• El concepto de semana traspasa la barrera del mes
• La barrera de mes es traspasada por los cortes
• Entre otros …
.
La esquematización tabular de la tabla nos puede ayudar a entrever mejor
Nos percatamos que está violando más de una regla de tabla de calendario estándar
Pese a que, no es una TCE es sencillo intuir que ella proporciona facilidades para elaborar cálculos que tengan que ver con esa académica, como: promedio de nota de un grupo en un tercio, días académicos en un tercio, etc.
Siguiendo el mismo arco, es fácil dar algunos ejemplos:
.
• Calendarios deportivos
• Calendarios de minoristas
• Calendarios ISO
.
Las tablas de calendario personalizadas requieren su propio tratamiento y no puede utilizar time intelligence clásicas
Hablaré más extensamente sobre ellas en un artículo futuro
La idea, en esencia, es que se no quede que las tablas de calendario estándar no son las únicas.
PUNTO IX: Generador De Tabla De Calendario Estándar
Para finalizar este artículo quiero comentar que tengo planeado crea un generador de tabla de calendario utilizando macros, pero que claro nos agilice ese proceso de construcción
Pero, para que su elaboración se haga realidad te quiero pedir un aporte, por favor:
– Comparte este artículo
– Comenta si ya has empezado piensas empezar a utilizar las tablas de calendario
Bueno, eso es todo de mi parte, gracias por leerme
– Hasta la próxima oportunidad
La entrada Construcción de tablas de calendario y porqué son la bomba aparece primero en Excel Free Blog.
Excel
via [Excel Free Blog] [http://ift.tt/2nXfycf]
March 26, 2017 at 04:02AM
No hay comentarios.:
Publicar un comentario