Seguiremos a partir del post previo donde profundizábamos en algunas funciones MDX dentro de nuestras funciones CUBO.
Hoy iremos un paso más allá y aplicaremos expresiones MDX en estas fantásticas funciones CUBO.
Veremos expresiones como FILTER, TOPCOUNT, EXISTS o EXCEPT, expresiones básicas o fundamentales para nuestro trabajo...
Desde nuestra base de datos cargada en el artículo anterior, con las relaciones entre tablas y medidas creadas, comenzaremos nuestro ejemplo.
En un primer ejemplo aplicaremos un filtro para recuperar miembros de 'País' cuyas ventas acumuladas superen los 1.700 euros
Así en C3 contruimos nuestro conjunto con la expresión Filter:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"Filter( [Tabla1].[País].Children, [Measures].[Sum_Ventas]>1700)";
"filtro>1700")
Si mostramos los miembros de nuesrto conjunto en C4:
/ =MIEMBRORANGOCUBO("ThisWorkbookDataModel";$C$3;SECUENCIA(RECUENTOCONJUNTOCUBO($C$3)))
veremos lo esperado, solo aquellos paises cuyas ventas sumen más de 1.700; i.e., como si en una tabla dinámica aplicáramos un filtro de valor sobre las ventas...
Incorporamos el valor de las ventas acumuladas con VALORCUBO en D$ y siguientes:
=VALORCUBO("ThisWorkbookDataModel";$E$15;$C4)
Si subimos el nivel de ventas a 3.700 comprobamos el efecto:
La sintaxis de Filter:
Filter(Set_Expression, Logical_Expression )
Nos pide en primer lugar que identifiquemos el conjunto de datos a obtener, según se cumpla la condición lógica del segunda argumento.
Veamos otra expresión interesante: TopCount.
Queremos obtener información de los dos paises con mayores ventas en el año 2025
Entonces montamos nuestro conjunto en C11:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"TopCount([Tabla1].[País].Children,2,([measures].[Sum_Ventas],[tabla1].[año].[2025]))";
"Top2_2025")
Por otra parte sacamos un MIEMBROGRUPO para la medida de la Suma de ventas (por variar... jeje). En D10 añadimos:
=MIEMBROCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]")
Y para listar los dos elementos obtenidos del conjunto montado con TopCount en C12 añadimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";C11;SECUENCIA(2))
Mientras que recuperamos las ventas acumuladas correspondientes con VALORCUBO en D12:
=VALORCUBO("ThisWorkbookDataModel";$D$10;$C12;"[Tabla1].[Año].&[2025]")
La sintaxis de TopCount:
TopCount(Set_Expression,Count [ ,Numeric_Expression ] )
Donde a partir del conjunto indicado, indicamos cuántos miembros por 'arriba' queremos extraer, según la expresión numérica (simple o compuesta) deseada.
Sobre nuestra expresión anterior:
TopCount([Tabla1].[País].Children,2,([measures].[Sum_Ventas],[tabla1].[año].[2025]))
indicamos que deseamos trabajar con los 'hijos' de País, con los dos más altos, que respondan al cruce de datos del año 2025 en cuanto a su suma de ventas....
La siguiente expresión es Exists, cuya sintáxis sería:
Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )
donde se definen un primer conjunto de miembros que exista en el segundo de ellos...
Veamos como ejemplo cómo obtener el listado de paises que existan en el año 2025
En la celda C17 añadimos nuestro conjunto
=CONJUNTOCUBO("ThisWorkbookDataModel";
"EXISTS([Tabla1].[País].Children, [Tabla1].[Año].["&B17&"])";
"pais-"&B17)
En C19 contamos el número de elementos del conjunto:
=RECUENTOCONJUNTOCUBO(C17)
Y en D17 y siguiente listamos dichos elementos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$C$17;SECUENCIA($C$19))
y finalmente recuperamos las ventas correspondientes en E17:E19
=VALORCUBO("ThisWorkbookDataModel";$E$15;D17;"[Tabla1].[Año].&["&$B$17&"]")
Otra expresión que conviene analizar es Except... Cuya sintaxis nos indica:
Except(Set_Expression1, Set_Expression2 [, ALL ] )
donde de la primera expresión o conjunto excluimos los del segundo... El tercer argumento ALL mantiene posibles duplicados del primer conjunto.
Nuestro ejemplo, trabajado previamente de manera similar en un artículo del blog previo, nos extraerá los Comerciales distintos a LUIS... en nuestro ejercicio de tres comerciales: ANA, EVA, LUIS, el conjunto será ANA + EVA
El resto de conjuntos y valores lo puedes encontrar en el artículo previo.
Otra expresión interesante, muy similar a Exists es NonEmpty:
NONEMPTY(set_expression1 [,set_expression2])
donde obtenemos miembros del primer conjunto con datos (no vacíos) en el segundo...
La diferencia con Exists es la prevalencia de duplicados... aunque son bastantes similares salvo que usemos el tercer argumento de Exists.
El conjunto formado en G17 es:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"NonEmpty([Tabla1].[País].Children, [Tabla1].[Año].["&B17&"])";
"pais-"&B17)
A partir del cual podemos obtener igualmente sus componentes con MIEMBRORANGOCUBO o datos de ventas con VALORCUBO.
Para concluir con esta introducción a MDX veremos un par de funciones mas: TopSum es BottomSum.
Funciones interesantes cuanto menos...
Sus sintaxis:
TopSum(Set_Expression, Value, Numeric_Expression)
La función calcula la suma de una medida (Numeric_Expresion) evaluda sobre el conjunto dado (Set_Expression) ordenado en descendente (de mayor a menor).
La función nos devolverá los elementos con los valores más altos cuyo total acumulado sea al menos el valor especificado (Value).
y
BottomSum(Set_Expression, Value, Numeric_Expression)
Esta función calcula la suma de una medida (Numeric_Expresion) evaluda sobre el conjunto dado (Set_Expression) ordenado en ascendente (de menor a mayor).
La función nos devolverá los elementos con los valores más bajos cuyo total acumulado sea al menos el valor especificado (Value).
Raro de entender, verdad?.
Veamoslo con un ejemplo.
Empezamos montando un conjunto con TopSum en la celda J7:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"TopSum( [Tabla1].[País].Children, 9868,[Measures].[Sum_Ventas])";
"TopSum 9868")
Al extraer sus miembros en J8:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$J$7;SECUENCIA(RECUENTOCONJUNTOCUBO($J$7)))
Y compararlos con la tabla dinámica de más abajo (ver imagen), comprobamos que los 'paises' listados corresponden a aquellos cuya suma acumulada (ordenados en sentido descendente) supera los 9.868... en este caso, los tres paises ES, FR, DE con mayor importe de ventas.
De forma similar con BottomSum en la celda N7:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"BottomSum( {[Tabla1].[País].Children}, 7453,[Measures].[Sum_Ventas])";
"BottomSum 7453")
Al extraer sus miembros en N8:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$N$7;SECUENCIA(RECUENTOCONJUNTOCUBO($N$7)))
De forma similar, al compararlos con la tabla dinámica de más abajo (ver imagen), comprobamos que los 'paises' listados corresponden a aquellos cuya suma acumulada (ordenados en sentido ascendente) supera los 7.453... en este caso, los dos paises PT, DE con menor importe de ventas..
Hoy iremos un paso más allá y aplicaremos expresiones MDX en estas fantásticas funciones CUBO.
Veremos expresiones como FILTER, TOPCOUNT, EXISTS o EXCEPT, expresiones básicas o fundamentales para nuestro trabajo...
En un primer ejemplo aplicaremos un filtro para recuperar miembros de 'País' cuyas ventas acumuladas superen los 1.700 euros
Así en C3 contruimos nuestro conjunto con la expresión Filter:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"Filter( [Tabla1].[País].Children, [Measures].[Sum_Ventas]>1700)";
"filtro>1700")
Si mostramos los miembros de nuesrto conjunto en C4:
/ =MIEMBRORANGOCUBO("ThisWorkbookDataModel";$C$3;SECUENCIA(RECUENTOCONJUNTOCUBO($C$3)))
veremos lo esperado, solo aquellos paises cuyas ventas sumen más de 1.700; i.e., como si en una tabla dinámica aplicáramos un filtro de valor sobre las ventas...
Incorporamos el valor de las ventas acumuladas con VALORCUBO en D$ y siguientes:
=VALORCUBO("ThisWorkbookDataModel";$E$15;$C4)
Si subimos el nivel de ventas a 3.700 comprobamos el efecto:
La sintaxis de Filter:
Filter(Set_Expression, Logical_Expression )
Nos pide en primer lugar que identifiquemos el conjunto de datos a obtener, según se cumpla la condición lógica del segunda argumento.
Veamos otra expresión interesante: TopCount.
Queremos obtener información de los dos paises con mayores ventas en el año 2025
Entonces montamos nuestro conjunto en C11:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"TopCount([Tabla1].[País].Children,2,([measures].[Sum_Ventas],[tabla1].[año].[2025]))";
"Top2_2025")
Por otra parte sacamos un MIEMBROGRUPO para la medida de la Suma de ventas (por variar... jeje). En D10 añadimos:
=MIEMBROCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]")
Y para listar los dos elementos obtenidos del conjunto montado con TopCount en C12 añadimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";C11;SECUENCIA(2))
Mientras que recuperamos las ventas acumuladas correspondientes con VALORCUBO en D12:
=VALORCUBO("ThisWorkbookDataModel";$D$10;$C12;"[Tabla1].[Año].&[2025]")
La sintaxis de TopCount:
TopCount(Set_Expression,Count [ ,Numeric_Expression ] )
Donde a partir del conjunto indicado, indicamos cuántos miembros por 'arriba' queremos extraer, según la expresión numérica (simple o compuesta) deseada.
Sobre nuestra expresión anterior:
TopCount([Tabla1].[País].Children,2,([measures].[Sum_Ventas],[tabla1].[año].[2025]))
indicamos que deseamos trabajar con los 'hijos' de País, con los dos más altos, que respondan al cruce de datos del año 2025 en cuanto a su suma de ventas....
La siguiente expresión es Exists, cuya sintáxis sería:
Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )
donde se definen un primer conjunto de miembros que exista en el segundo de ellos...
Veamos como ejemplo cómo obtener el listado de paises que existan en el año 2025
En la celda C17 añadimos nuestro conjunto
=CONJUNTOCUBO("ThisWorkbookDataModel";
"EXISTS([Tabla1].[País].Children, [Tabla1].[Año].["&B17&"])";
"pais-"&B17)
En C19 contamos el número de elementos del conjunto:
=RECUENTOCONJUNTOCUBO(C17)
Y en D17 y siguiente listamos dichos elementos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$C$17;SECUENCIA($C$19))
y finalmente recuperamos las ventas correspondientes en E17:E19
=VALORCUBO("ThisWorkbookDataModel";$E$15;D17;"[Tabla1].[Año].&["&$B$17&"]")
Otra expresión que conviene analizar es Except... Cuya sintaxis nos indica:
Except(Set_Expression1, Set_Expression2 [, ALL ] )
donde de la primera expresión o conjunto excluimos los del segundo... El tercer argumento ALL mantiene posibles duplicados del primer conjunto.
Nuestro ejemplo, trabajado previamente de manera similar en un artículo del blog previo, nos extraerá los Comerciales distintos a LUIS... en nuestro ejercicio de tres comerciales: ANA, EVA, LUIS, el conjunto será ANA + EVA
El resto de conjuntos y valores lo puedes encontrar en el artículo previo.
Otra expresión interesante, muy similar a Exists es NonEmpty:
NONEMPTY(set_expression1 [,set_expression2])
donde obtenemos miembros del primer conjunto con datos (no vacíos) en el segundo...
La diferencia con Exists es la prevalencia de duplicados... aunque son bastantes similares salvo que usemos el tercer argumento de Exists.
El conjunto formado en G17 es:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"NonEmpty([Tabla1].[País].Children, [Tabla1].[Año].["&B17&"])";
"pais-"&B17)
A partir del cual podemos obtener igualmente sus componentes con MIEMBRORANGOCUBO o datos de ventas con VALORCUBO.
Para concluir con esta introducción a MDX veremos un par de funciones mas: TopSum es BottomSum.
Funciones interesantes cuanto menos...
Sus sintaxis:
TopSum(Set_Expression, Value, Numeric_Expression)
La función calcula la suma de una medida (Numeric_Expresion) evaluda sobre el conjunto dado (Set_Expression) ordenado en descendente (de mayor a menor).
La función nos devolverá los elementos con los valores más altos cuyo total acumulado sea al menos el valor especificado (Value).
y
BottomSum(Set_Expression, Value, Numeric_Expression)
Esta función calcula la suma de una medida (Numeric_Expresion) evaluda sobre el conjunto dado (Set_Expression) ordenado en ascendente (de menor a mayor).
La función nos devolverá los elementos con los valores más bajos cuyo total acumulado sea al menos el valor especificado (Value).
Raro de entender, verdad?.
Veamoslo con un ejemplo.
Empezamos montando un conjunto con TopSum en la celda J7:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"TopSum( [Tabla1].[País].Children, 9868,[Measures].[Sum_Ventas])";
"TopSum 9868")
Al extraer sus miembros en J8:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$J$7;SECUENCIA(RECUENTOCONJUNTOCUBO($J$7)))
Y compararlos con la tabla dinámica de más abajo (ver imagen), comprobamos que los 'paises' listados corresponden a aquellos cuya suma acumulada (ordenados en sentido descendente) supera los 9.868... en este caso, los tres paises ES, FR, DE con mayor importe de ventas.
De forma similar con BottomSum en la celda N7:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"BottomSum( {[Tabla1].[País].Children}, 7453,[Measures].[Sum_Ventas])";
"BottomSum 7453")
Al extraer sus miembros en N8:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$N$7;SECUENCIA(RECUENTOCONJUNTOCUBO($N$7)))
De forma similar, al compararlos con la tabla dinámica de más abajo (ver imagen), comprobamos que los 'paises' listados corresponden a aquellos cuya suma acumulada (ordenados en sentido ascendente) supera los 7.453... en este caso, los dos paises PT, DE con menor importe de ventas..
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.