La semana pasada publiqué un post donde resolvía un problema interesante con la función SUMAPRODUCTO, en aquel ejercicio veíamos como replicar el comportamiento de las funciones BD trabajando sobre un rango de criterios variables!!.
Hoy veremos cómo aplicando las funciones CUBO y expresiones MDX podremos repetir el ejercicio.
En particular necesitaremos emplear la función MDX Union (vista en el post previo), en alguna de sus formas alternativas...
Además nos aprovecharemos de una de las nuevas funciones (disponibles para Microsoft 365) liberadas hace poco tiempo:
MATRIZATEXTO(matriz; [formato])
función que devuelve una matriz de valores de texto de cualquier rango especificado.
Pasa los valores de texto sin modificarlos y convierte los valores no textuales en texto.
Partiremos de nuestra base de datos ('tabla1') cargada en el Modelo de datos (Power Pivot); con unas medidas creadas en dicho Modelo:
Sum_Ventas:=SUM([Ventas])
Cuenta_Ventas:=COUNTA([Ventas])
Revisa los artículos previos a este, donde se detallan los pasos y acciones seguidas.
En la imagen vemos todos los cálculos realizados con la función SUMAPRODUCTO del post anterior; y además el desarrollo realizado con las funciones CUBO.
OJO!!. Actualización: Para mejorar la fórmula y evitar duplicidades con criterios no excluyentes, he cambiado la referencia de .Children por .[All]
En primer lugar en la celda P5, con la función CONJUNTOCUBO deberemos escribir cuál es el conjunto requerido...
Si expresaramos literalmente el ejemplo de la imagen, esto es, que sea:
registros que coincidan simultáneamente con el 'Comercial = ANA' y además 'País= ES'
más aquellos registros con el 'Comercial = EVA' y 'Producto = P4'
y más aquellos registros con el 'Comercial = EVA' y 'Producto = P2'
Si tuvieramos que desarrollar la expresión en MDX podría tener esta forma:
"{[Tabla1].[Comercial].[ANA]}*{[Tabla1].[País].[ES]}*{[Tabla1].[Producto].[All]},
{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{[Tabla1].[Producto].[P4]},
{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{[Tabla1].[Producto].[P2]}"
forma alternativa de uso de la función MDX Union.
El problema es que tal cual no sería viable por que excederíamos el límite de 255 caracteres en un argumento de función!!! :O
Por lo que podríamos mejorar un poco la expresión hasta:
"{{{[Tabla1].[Comercial].[ANA]}*{[Tabla1].[País].[ES]}*{[Tabla1].[Producto].[All]}},
{{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{{[Tabla1].[Producto].[P2]},{[Tabla1].[Producto].[P4]}}}}"
más concreta y reducida para este caso en particular.
Claro está, si queremos que el rango de criterios sea variable, nuestra expresión de conjunto en MDX, no puede ser tan rígida como la anteior, válida solo para esa distribución inicial... por lo que deberemos buscar una forma para retornar de forma dinámica una expresión de conjunto en MDX.
Nota que la función Union, en esta forma, requiere el asterisco (* multiplicación) como operador lógico Y, y la coma (,) como operador lógico O.
Esto lo conseguiremos con la función MATRIZATEXTO
Veamos los pasos...
Comprobamos como nos devuelve una cadena de texto con estructura de matriz a partir de los valores del rango (OJO con los separadores de filas y columnas de tu sistema!!!, en mi caso la \ para columnas, y el ; para las filas)
La 'matriz' devuelva es: {"ANA"\"ES"\;"EVA"\\"P4";"EVA"\\"P2"}
tenemos claramente identificados los elementos por filas y por columnas.
Por lo que el siguiente paso consistiría en asociar a cada elemento su cadena de texto requerida para la expresión en MDX; esto es, identificando la tabla ,el campo y el valor, con sus corchetes necesarios.
Así pues, generaremos un rango de criterios 'virtual' con una función SI condicional, que cambie los elementos originales por las cadenas de textos necesarios... en nuestro ejemplo:
=SI($B$3:$D$5="";
"{[Tabla1].["&B$2:$D$2&"].[All]}";
"{"&SI($B$3:$D$5<>0;
"[Tabla1].["&B$2:D$2&"].["&$B$3:$D$5&"]";
"[Tabla1].["&B$2:$D$2&"].[All]")&"}")
Observamos que nuestro condicional se encarga de celda a celda montar las referencias en MDX necesarias, a base de concatenados de los encabezados con los elementos individuales de cada fila y /o columna.
Ahora sí, sobre esta matriz virtual, aplicamos la función MATRIZATEXTO tendríamos la siguiente cadena:
{"{[Tabla1].[Comercial].[ANA]}"\"{[Tabla1].[País].[ES]}"\"{[Tabla1].[Producto].[All]}";"{[Tabla1].[Comercial].[EVA]}"\"{[Tabla1].[País].[All]}"\"{[Tabla1].[Producto].[P4]}";"{[Tabla1].[Comercial].[EVA]}"\"{[Tabla1].[País].[All]}"\"{[Tabla1].[Producto].[P2]}"}
Estamos cerca ya...
Si echamos un vistazo a la última cadena de texto obtenida, facilmente se observa la similitud con la expresión final buscada... solo hay que sustituir las \ por *, y los ; por , (insisto aquí, OJO con los separadores de tu sistema!!).
Por tanto, el último paso consistiría en reemplazar o SUSTITUIR dichos caracteres:
=SUSTITUIR(SUSTITUIR(SUSTITUIR(B13;"\";"*");";";",");"""";"")
Listo!!, objetivo conseguido!!. Tenemos formulado, a partir del rango de criterios, un generador dinámico de expresiones MDX para crear un 'Conjunto'.
Con todo esto ya podemos volver a nuestra hoja de cálculo, e insertar en P5 la fórmula:
=CONJUNTOCUBO("ThisWorkbookDataModel";
SUSTITUIR(SUSTITUIR(SUSTITUIR(
MATRIZATEXTO(
SI($L$2:$N$4="";
"{[Tabla1].["&L$1:$N$1&"].[All]}";
"{"&SI($L$2:$N$4<>0;
"[Tabla1].["&L$1:N$1&"].["&$L$2:$N$4&"]";
"[Tabla1].["&L$1:$N$1&"].[All]")&"}");1);"\";"*");";";",");"""";"");"Criterios")
Con el conjunto creado, podemos hacer uso de él en las celdas P6 y P7, con la función VALORCUBO y llamando a las medidas de nuestro Modelo de datos:
En P6 tendríamos el dato de cuántos registros verifican o existen en el conjunto creado:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Cuenta_Ventas]";P5)
Mientras que en P7 calcularíamos los acumulados de las ventas de dicho conjunto:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";P5)
podemos comparar rapidamente los resultados con los datos obtenidos con las fórmulas SUMAPRODUCTO, SUMAR.SI.CONJUNTO o BDSUMA/BDCONTARA... Fantástico resultado!!.
De hecho incluso más dinámico que con las funciones clásicas (SUMAPRODUCTO o SUMAR.SI.CONJUNTO!!)
Pero aún nos queda desplegar el cálculo para el periodo de estudio, entre los años 2013 y 2019.
Así en la celda P8 creamos un nuevo conjunto:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2019]";
"Años-13_19")
Expresión que delimita el intervalo entre un dato y otro (usando los dos puntos como operador).
A partir de este último conjunto, desplegamos los elementos en O10 con la fórmula:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";P8;SECUENCIA(RECUENTOCONJUNTOCUBO(P8)))
Para acabar finalmente nuestro ejercicio, incluyendo en P10 la fórmula:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";$P$5;O10)
y arrastrar hasta P16.
Nuevamente, las funciones CUBO, combinada en este caso con la función MATRIZATEXTO, nos ofrece una posibilidad de cálculo sobre bases de datos cargadas en nuestros Modelos de datos increibles... más allá de simples tablas dinámicas.
Hoy veremos cómo aplicando las funciones CUBO y expresiones MDX podremos repetir el ejercicio.
En particular necesitaremos emplear la función MDX Union (vista en el post previo), en alguna de sus formas alternativas...
Además nos aprovecharemos de una de las nuevas funciones (disponibles para Microsoft 365) liberadas hace poco tiempo:
MATRIZATEXTO(matriz; [formato])
función que devuelve una matriz de valores de texto de cualquier rango especificado.
Pasa los valores de texto sin modificarlos y convierte los valores no textuales en texto.
Partiremos de nuestra base de datos ('tabla1') cargada en el Modelo de datos (Power Pivot); con unas medidas creadas en dicho Modelo:
Sum_Ventas:=SUM([Ventas])
Cuenta_Ventas:=COUNTA([Ventas])
Revisa los artículos previos a este, donde se detallan los pasos y acciones seguidas.
En la imagen vemos todos los cálculos realizados con la función SUMAPRODUCTO del post anterior; y además el desarrollo realizado con las funciones CUBO.
OJO!!. Actualización: Para mejorar la fórmula y evitar duplicidades con criterios no excluyentes, he cambiado la referencia de .Children por .[All]
En primer lugar en la celda P5, con la función CONJUNTOCUBO deberemos escribir cuál es el conjunto requerido...
Si expresaramos literalmente el ejemplo de la imagen, esto es, que sea:
registros que coincidan simultáneamente con el 'Comercial = ANA' y además 'País= ES'
más aquellos registros con el 'Comercial = EVA' y 'Producto = P4'
y más aquellos registros con el 'Comercial = EVA' y 'Producto = P2'
Si tuvieramos que desarrollar la expresión en MDX podría tener esta forma:
"{[Tabla1].[Comercial].[ANA]}*{[Tabla1].[País].[ES]}*{[Tabla1].[Producto].[All]},
{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{[Tabla1].[Producto].[P4]},
{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{[Tabla1].[Producto].[P2]}"
forma alternativa de uso de la función MDX Union.
El problema es que tal cual no sería viable por que excederíamos el límite de 255 caracteres en un argumento de función!!! :O
Por lo que podríamos mejorar un poco la expresión hasta:
"{{{[Tabla1].[Comercial].[ANA]}*{[Tabla1].[País].[ES]}*{[Tabla1].[Producto].[All]}},
{{[Tabla1].[Comercial].[EVA]}*{[Tabla1].[País].[All]}*{{[Tabla1].[Producto].[P2]},{[Tabla1].[Producto].[P4]}}}}"
más concreta y reducida para este caso en particular.
Claro está, si queremos que el rango de criterios sea variable, nuestra expresión de conjunto en MDX, no puede ser tan rígida como la anteior, válida solo para esa distribución inicial... por lo que deberemos buscar una forma para retornar de forma dinámica una expresión de conjunto en MDX.
Nota que la función Union, en esta forma, requiere el asterisco (* multiplicación) como operador lógico Y, y la coma (,) como operador lógico O.
Esto lo conseguiremos con la función MATRIZATEXTO
Veamos los pasos...
Comprobamos como nos devuelve una cadena de texto con estructura de matriz a partir de los valores del rango (OJO con los separadores de filas y columnas de tu sistema!!!, en mi caso la \ para columnas, y el ; para las filas)
La 'matriz' devuelva es: {"ANA"\"ES"\;"EVA"\\"P4";"EVA"\\"P2"}
tenemos claramente identificados los elementos por filas y por columnas.
Por lo que el siguiente paso consistiría en asociar a cada elemento su cadena de texto requerida para la expresión en MDX; esto es, identificando la tabla ,el campo y el valor, con sus corchetes necesarios.
Así pues, generaremos un rango de criterios 'virtual' con una función SI condicional, que cambie los elementos originales por las cadenas de textos necesarios... en nuestro ejemplo:
=SI($B$3:$D$5="";
"{[Tabla1].["&B$2:$D$2&"].[All]}";
"{"&SI($B$3:$D$5<>0;
"[Tabla1].["&B$2:D$2&"].["&$B$3:$D$5&"]";
"[Tabla1].["&B$2:$D$2&"].[All]")&"}")
Observamos que nuestro condicional se encarga de celda a celda montar las referencias en MDX necesarias, a base de concatenados de los encabezados con los elementos individuales de cada fila y /o columna.
Ahora sí, sobre esta matriz virtual, aplicamos la función MATRIZATEXTO tendríamos la siguiente cadena:
{"{[Tabla1].[Comercial].[ANA]}"\"{[Tabla1].[País].[ES]}"\"{[Tabla1].[Producto].[All]}";"{[Tabla1].[Comercial].[EVA]}"\"{[Tabla1].[País].[All]}"\"{[Tabla1].[Producto].[P4]}";"{[Tabla1].[Comercial].[EVA]}"\"{[Tabla1].[País].[All]}"\"{[Tabla1].[Producto].[P2]}"}
Si echamos un vistazo a la última cadena de texto obtenida, facilmente se observa la similitud con la expresión final buscada... solo hay que sustituir las \ por *, y los ; por , (insisto aquí, OJO con los separadores de tu sistema!!).
Por tanto, el último paso consistiría en reemplazar o SUSTITUIR dichos caracteres:
=SUSTITUIR(SUSTITUIR(SUSTITUIR(B13;"\";"*");";";",");"""";"")
Listo!!, objetivo conseguido!!. Tenemos formulado, a partir del rango de criterios, un generador dinámico de expresiones MDX para crear un 'Conjunto'.
Con todo esto ya podemos volver a nuestra hoja de cálculo, e insertar en P5 la fórmula:
=CONJUNTOCUBO("ThisWorkbookDataModel";
SUSTITUIR(SUSTITUIR(SUSTITUIR(
MATRIZATEXTO(
SI($L$2:$N$4="";
"{[Tabla1].["&L$1:$N$1&"].[All]}";
"{"&SI($L$2:$N$4<>0;
"[Tabla1].["&L$1:N$1&"].["&$L$2:$N$4&"]";
"[Tabla1].["&L$1:$N$1&"].[All]")&"}");1);"\";"*");";";",");"""";"");"Criterios")
Con el conjunto creado, podemos hacer uso de él en las celdas P6 y P7, con la función VALORCUBO y llamando a las medidas de nuestro Modelo de datos:
En P6 tendríamos el dato de cuántos registros verifican o existen en el conjunto creado:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Cuenta_Ventas]";P5)
Mientras que en P7 calcularíamos los acumulados de las ventas de dicho conjunto:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";P5)
podemos comparar rapidamente los resultados con los datos obtenidos con las fórmulas SUMAPRODUCTO, SUMAR.SI.CONJUNTO o BDSUMA/BDCONTARA... Fantástico resultado!!.
De hecho incluso más dinámico que con las funciones clásicas (SUMAPRODUCTO o SUMAR.SI.CONJUNTO!!)
Pero aún nos queda desplegar el cálculo para el periodo de estudio, entre los años 2013 y 2019.
Así en la celda P8 creamos un nuevo conjunto:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2019]";
"Años-13_19")
Expresión que delimita el intervalo entre un dato y otro (usando los dos puntos como operador).
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";P8;SECUENCIA(RECUENTOCONJUNTOCUBO(P8)))
Para acabar finalmente nuestro ejercicio, incluyendo en P10 la fórmula:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";$P$5;O10)
y arrastrar hasta P16.
Nuevamente, las funciones CUBO, combinada en este caso con la función MATRIZATEXTO, nos ofrece una posibilidad de cálculo sobre bases de datos cargadas en nuestros Modelos de datos increibles... más allá de simples tablas dinámicas.
