Producto Cartesiano con Tablas Dinámicas

Un concepto obastante interesante a la hora de relacionar distintas fuentes es el Producto Cartesiano.
Una sencilla definición de lo que implica este producto cartesiana sería la que encontramos en Wikipedia:
el producto cartesiano de dos conjuntos es una operación, que resulta en otro conjunto, cuyos elementos son todos los pares ordenados que pueden formarse de forma que el primer elemento del par ordenado pertenezca al primer conjunto y el segundo elemento pertenezca al segundo conjunto.
En definitiva, el producto cartesiano nos devuelve todas las combinaciones posibles entre los elementos de los conjuntos implicados...

El uso, último recurso, de esta operación suele ser relacionar tablas entre sí, las cuales no tiene ningún campo en común... pero que si necesitamos 'unir' para realizaar ciertos cálculos..

Hoy sencillamente realizaremos esta operación para obtener un listado de todas esas coombinaciones posibles entre tres listados.
IMPORTANTE PRECAUCIÓN!!, ya que lo que vamos a obtener es un listado de todas las combinaciones posibles, debderemos tener especial cuidado con el número de elementos de los listados a operar...
Un ejemplo sencillo, si realizamos el producto cartesiano de dos listados, uno de 10.000 elementos, con un segundo listado de 1.000 elementos, el conjunto resultante tendrá: 10.000 x 1.000 = 10.000.000 de registros!!!.
OJO por que esto puede (y lo hará) ralentizar todos nuestros procesos de cálculo.

Pues hechas las advertencias oportunas, veremos cómo conseguir nuestro listado de combinaciones empleando tablas dinámicas!.
Tenemos tres listados o tablas con diferente información: países, colores y números
La meta será conseguir el listado único de todas las combinaciones posibles

Veamos cómo de simple son los pasos para lograr este resultado.
Partimos de una de nuestras tres tablas (cualquiera vale), por ejemplo la de 'países' y la seleccionaremos/activaremos.
El paso siguiente es clave, ya que será en el momento que Insertemos la tabla dinámica donde deberemos marcar una opción importante.
En concreto deberemos selecciona la opción de: Agregar estos datos al modelo de datos

Únicamente realizaremos esta acción sobre una de nuestras fuentes!!!.
Con la tabla dinámica ya creada, iremos al panel de la lista de campos, y seleccionaremos la opción de 'Todas':

Por 'arte de magia' veremos nuestras tres tablas!!! (ojo, sin relación alguna!!).
En el paso siguiente, muy sencillo, solo llevaremos al área de filas, los distintos campos únicos de nuestros listados...

Ya tenemos el listado buscado... solo nos queda dar un poco de estética (desde la barra de herramientas de Diseño de la tabla dinámica).
1-Cambiaremos el diseño de informe a un modelo en formato tabular.
2-Desactivaremos los Totales generales para filas y columnas.
3-Marcaremos la opción de 'Repetir todas las etiquetas de elementos'.
Listo!!!

Seguir leyendo...

LAMBDA: Lista de distintos

Hoy veremos una de esas fórmulas que resultan útiles y que por su simpleza te sorprenderá. Construiremos una fórmula que nos liste elementos distintos entre dos listas...

El caso es que recientemente tuve la necesidad de obtener una lista de elementos previamente no empleados, y aunque en ese momento la generé 'manualmente', me quedé pensando en la fórmula que automatizara tal cosa...
Y aquí está el resultado.

Partimos de una 'Lista_A' principal con todos los elementos posibles... y una 'Lista_B' con los elementos ya utilizados. El objetivo es obtener una lista de aquellos de la primera no usados aún, i.e., una lista de distintos.

Nuestra fórmula en F2 sería:

=UNICOS(FILTRAR(Lista_A;
        LET(num;CONTARA(List_B);
            nFil;CONTARA(Lista_A);
            arrDist;--(Lista_<>TRANSPONER(List_B));
        MMULT(arrDist;SECUENCIA(num;1;1;0))>(num-1))))

Donde generamos una matriz de 1/VERDADEROS y 0/FALSOS para cada elemento de la 'Lista_A', que nos indicará si se existe en la 'Lista_B' o no.

Para llegar a ese vector de V-F he optado por un clásico método empleando la función MMULT que multiplica matrices...
Finalmente la función FILTRAR se encarga de listar dichos elementos... Todo dentro de la función UNICOS para eliminar posibles repeticiones.

Pero si te gustan las nuevas funciones LAMBDA entonces esta segunda opción es la tuya:

   =FILTRAR(Lista_A;
            BYROW(Lista_A;LAMBDA(rw;CONTAR.SI(List_B;rw)=0)))

La función BYROW se encarga esta vez de evaluar las coincidencias o no entre las listas, y obtener el vector de Vy F necesarios para FILTRAR.
Más simple es imposible ;-)

Seguir leyendo...

SI.CONJUNTO-FILTRAR-SI aplica condiciones

Revisando pendientes me dí cuenta que no había escrito mucho sobre algunas funciones...
Y una que merece una especial atención sin duda es SI.CONJUNTO (echa un vistazo...).
Una función que lleva con nosotros desde 2016 y aún es una gran desconocida.

De paso que expongo el caso, aprovecharé para hacer una comparativa (o dar alternativas con otras funciones 'viejas' o 'nuevas').
Partiremos de una tabla con registros de empleados...

El objetivo es recuperar los valores de las jornadas, según condiciones, dados en el rango H2:I6, y determinar para cada registro de la tabla qué número de horas corresponde...

Analicemos en primer lugar las condiciones requeridas mediante un esquema de árbol de decisión:

Esquema que debemos asegurarnos que es correcto, ya que basaremos nuestro algoritmo en él...

En consecuencia, y como primera opción, añadimos a nuestra tabla de empleados el siguiente campo formulado:

=SI([@[Centro trabajo]]="FAB";
        SI([@Turno]="M";
              7;
              SI([@Turno]="N";6,5;8));
        SI([@[Centro trabajo]]="ALM";
               SI([@Turno]="N";7,5;8,5);
               8,5))

Fíjate que solo sigue la línea desarrollada en el esquema de árbol anterior...

Una alternativa sería desarrollar las condiciones en orden, siguiendo una 'cascada' de pruebas lógicas, tal cual nos van apareciendo...

=SI(Y([@[Centro trabajo]]="FAB";[@Turno]="m");
       7;
       SI(Y([@[Centro trabajo]]="FAB";[@Turno]="N");
           6,5;
           SI([@[Centro trabajo]]="FAB";
               8;
              SI(Y([@[Centro trabajo]]="ALM";[@Turno]="N");
                 7,5;
                 8,5))))

En este caso fíjate como solo vamos 'fluyendo' por las diferentes pruebas lógicas múltiples (gestionadas con un Y), dando sus respuestas de verdadero y falso...

A partir de las formas de proceder anteriores llegamos a la función SI.CONJUNTO, donde su estructura nos propone una alternancia de argumentos: prueba_lógica y salida de verdadero (SOLO verdadero, no da respuesta de falso directamente!!).
Además, según añadimos más y más pares de argumentos, los posteriores aplicarán solo en el caso de que no se hayan verificado las previas.
En nuestro ejemplo, añadimos nuevo campo formulado:

=SI.CONJUNTO([@[Centro trabajo]]="FAB";SI.CONJUNTO([@Turno]="M";7;
    						[@Turno]="N";6,5;
    						VERDADERO;8);
Y([@[Centro trabajo]]="ALM";[@Turno]="N");7,5;
VERDADERO;8,5)

Notemos cómo al anidar un nuevo SI.CONJUNTO como respuesta a la primera prueba lógica (centro_trabajo='FAB') conseguimos dar respuestas controladas para los distintos casos planteados...
Recuerda que la forma, con SI.CONJUNTO, de considerar el resto de casos no evaluados en las condiciones previas, es dando como prueba lógica un VERDADERO, seguido como respuesta el valor deseado...

PLUS sorprendente.
Una última opción que os propongo es solucionarlo empleando la función FILTRAR, apoyándome en un rango de criterios auxiliar, que represente inequívocamente las condiciones a verificar.
Incorporamos un último campo formulado a nuestra tabla de empleados...

=FILTRAR(Tabla2[Horas];
                   (Tabla2[CT]=[@[Centro trabajo]])*(SI.ERROR(HALLAR([@Turno];Tabla2[Turno])>0;0));
                   8,5)

La fórmula se basa en la idea de buscar (HALLAR) los turnos de cada registro en la tabla auxiliar (en la columna correspondiente), junto a la condición del 'centro de trabajo', claro... Es importante construir y transformar correctamente el rango auxiliar de condiciones... bastará ir condición a condición completando cada columna con su valor de campo correspondiente...
Siendo necesario definir listados excluyentes de 'turnos' para cubrir el caso de 'otros', como por ejemplo en la condición de 'FAB y otros turnos'

Con una buena definición de criterios en nuestro rango auxiliar conseguimos resultados únicos, dando salida a la opción 'cualquier otro caso' (última prueba lógica) con el tercer argumento de la función FILTRAR '[if_empty]'
:OOO

Seguir leyendo...

Funciones CUBO-LAMBDA y MDX

LLevaba unas semanas con ganas de probar la compatibilidad de dos de mis categorías de funciones preferidas: LAMBDA y CUBO (y MDX impícito), así que me puse a jugar con ambas, acabando con una agradable sensación...

Si te interesa este tema te recomiendo al menos leas estos tres artículos del blog:
- un primer que habla entre otras cosas de la función MATRIZATEXTO, y cómo nos puede facilitar obtener las cadenas de MDX.
- un segundo y tercer que hablan sobre funciones en MDX como Exists y NonEmpty
NonEmpty
NonEmpty

Vamos a aplicar sobre un ejemplo concreto, donde a partir de una tabla con información, cargada al modelo de datos en PowerPivot, y donde hemos añadido una Medida:
Sum_Total:=SUM([Total])
Y que tenemos por objetivo:
1-Listar nombres de clientes que cumplan las condiciones dadas (triple criterio: País + Producto + Comercial)
2-Obtener el dato de los importes correspondientes.

Lo vemos en la imagen siguiente:

Vemos la base de datos cargada en el modelo de datos (tabla en azul llamada TblVENTAS), y en negro las tres tablas que servirán para perfilar la búsqueda de criterios (TblPAIS, TblPDTO y TblCOMERCIAL respectivamente).
Fíjate que he añadido algunos elementos inexistentes en la base de datos!!... ya que parte del trabajo evitará fallos a este respecto.

La primera parte consistirá en generar tres cadenas de MDX para que conjunto de criterios (país, producto y comerciales), Una vez obtenida la cadena de texto la incluiremos dentro de la función CONJUNTOCUBO correspondiente.
Por tanto, escribiremos en K9:

=LET(VbUSCADO;TblPAIS[Elige paises];
           txtMDX;"[TblVENTAS].[País].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[País];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

De forma similar para los productos, en K11:

=LET(VbUSCADO;TblPDTO[Elige producto];
           txtMDX;"[TblVENTAS].[Producto].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[Producto];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

Acabamos en K13 para los comerciales:

=LET(VbUSCADO;TblCOMERCIAL[Elige comercial];
           txtMDX;"[TblVENTAS].[Comercial].&[" & VbUSCADO & "]";
          txtMDXCond;SI(ESERROR(COINCIDIR(VbUSCADO;TblVENTAS[Comercial];0));  "";  txtMDX);
 "{" & UNIRCADENAS(",";VERDADERO;txtMDXCond) &"}")

Revisa los enlaces del principio del artículo para entender qué se conseguirá con estas cadenas de texto MDX.
Basicamente lista los elementos seleccionados en las tablas de condiciones..., pero ligadas a la TblVENTAS.
Igualmente fíjate cómo evitamos el fallo para los elementos inexistentes.. un simple SI condicional con ESERROR.

Si incluimos estas cadenas de MDX dentro de la función CONJUNTOCUBO le aportamos un significada de segmentación de la base de datos...
Así en L10 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$9;"paises")
que formará el conjunto de datos de los países elegidos...
En L12 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$11;"pdtos")
que formará el conjunto de datos de los prodcutos elegidos...
Y en L14 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";$K$13;"comerciales")
que formará el conjunto de datos de los comerciales elegidos...

De momento tres subconjuntos de datos de la tabla de ventas...

Construiremos un subconjunto como intersección de los tres anteriores que nos devuelva los elementos del campo 'Cliente' que corresponda.
En K17,empleando la función MDX NonEmpty, escribimos:

=CONJUNTOCUBO("ThisWorkbookDataModel";
"NONEMPTY( [TblVENTAS].[Cliente].children, (" & $K$9 & "," & K11 &","& K13 &") )";
"clientes")

Esta función retornará los clientes, no vacíos, que cumplan al mismo tiempo las tres condiciones de los conjuntos anteriores...
Lo que podemos ver en forma de lista desbordada en K19:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";K17;SECUENCIA(RECUENTOCONJUNTOCUBO(K17)))
la función SECUENCIA se encarga de desbordar los diferentes miembros del conjunto anterior de clientes...

Primera parte lograda!!.

La segunda consiste en saber para cada cliente listado cuántos euros suman, obviamente solo de aquellos registros que cumplan los criterios dados.
Entonces en L19 y trabajando sobre los conjuntos y subconjuntos anteriores escribimos:

=LET(numEltos;RECUENTOCONJUNTOCUBO(K17);
        BYROW(SECUENCIA(numEltos);
              LAMBDA(rw;
                     VALORCUBO("ThisWorkbookDataModel"; INDICE($K$19#;rw);$L$10;$L$12;$L$14;"[Measures].[Sum_Total]"))
                       )
          )

donde la función BYROW se encarga de recorrer los diferentes clientes listados, para ir aplicando a cada uno (A CADA MIEMBRO) la función VALORCUBO que devolverá el dato de la medida generada en el modelo de datos (recuerda los primeros pasos!!) cuando se cumplan los tres criterios: país, producto y comercial.

Conclusión... buenas migas entre unas y otras (funciones LAMBDA y CUBO), y nada que reclamar 👍

Seguir leyendo...