• 👏 Bienvenido a nuestra comunidad Excel

    ¿Todavía no estás registrado? 😲

    Registrate gratis aquí y podrás:

    💪 Hacer preguntas a los expertos
    ⬇️ Descargar ejemplos y plantillas
    🏅 
    Acceder a contenidos premium

Contar Clientes que han comprado en 2 o 3 años

Sanlia

New member
Buenas tardes a todos,

Os escribo porque tengo una duda sobre cómo hacer una fórmula en power pivot, creo que será sencilla, pero no logro dar con ella.

Tengo un caso de una tabla que sólo tiene 4 columnas, una con el id de cliente, otra con las fechas en que ha comprado (tanto los clientes como las fechas se repiten), otra con las facturas y otra con los años fiscales. Son datos de 3 años. Lo que quiero conseguir es contar el número de clientes únicos que han comprado en 3 años fiscales diferentes (los años fiscales en mi caso van de oct a sep) y el número de clientes únicos que han comprado dos años. Al intentar usar filter, no me funciona por el contexto de fila que no puedo o no sé indicar que sean los 3 años a la vez o los 2 años a la vez. Supongo que habría que hacerlo creando varios contextos de fila programables, pero no tengo claro cómo hacerlo. Os agradecería si me pudierais dar alguna pista.

Adjunto fichero de ejemplo con tabla y resultados.

Muchas gracias
 

Adjuntos

Héctor Miguel

Well-known member
alguna alternativa que no incluya "agregar al modelo de datos" (el tamaño del archivo paso de 16 Kb a 117 Kb) ?

que pasara cuando el volumen de datos sea mayor ?
 

Gabriel Raigosa

Active member
He hecho la siguente prueba:

He tomado un archivo con una base de datos de 223.255 registros y 22 columnas, solo dos de ellas con formulas, tamaño del archivo 26.81 MB

Archivo 01 base de datos sin tablas dinámicas
Archivo 02 base de datos y una tabla dinámica sencilla, pero con la opción por defecto de guardar datos de origen con el archivo
Archivo 03 base de datos y una tabla dinámica con la opción de "agregar estos al modelo de datos"
Archivo 04 base de datos y una tabla dinámica con la opción de "agregar estos al modelo de datos", pero formato xlsb

En este caso en particular el uso de la opción del modelo de datos no fué desventajoso.

Ahora si se desactiva la opción de "no guardar los datos de origen", el archivo 02 es casi del mismo tamaño que el archivo 01.

1596419947303.png

Habria que considerar ventajas/desventajas entre usar una u otra opción.

Siempre es interesante mejorar/optimizar los procesos de cálculo en los libros de gran tamaño.

Se escuchan (leen...) recomendaciones.

Saludos
 

Héctor Miguel

Well-known member
Se escuchan (leen...) recomendaciones
del "modelo de datos"... empezaria por analizar la diferencia de tamaños entre la muestra original (16Kb) y con el modelo de datos agregado (117Kb) de este hilo
una cosa es "solo agregar" (al modelo de datos) y otra es empezar a usar "sus bondades" (cuando ocurren los "impactos" de los cubos)

1) puedes imaginarlo como un "cubo de rubik" desde el que puedes consultar (cada "cubito" de) tus datos en cualquier sentido, orden, dimension, etc. por esto...

2) según el volumen y disposicion de tus datos y lo que "el modelo" pueda considerar como "aprovechable" para (y por si requieres de) analisis posteriores, puede hacer que tu archivo crezca desproporcionadamente

3) esa "crecidita" puede andar por el orden de 8 a 50 veces (o mas) del tamaño de un archivo SIN "agregar al modelo de datos"
- una vez que empiezas a usar alguna de sus funciones extras (como el recuento distinto en TD "normales")

4) como "buen gestor de bases de datos relacionales", el modelo de datos agradece estructuras "perfectas" en sus bases, de lo contrario... (?)

aqui te hablan (por supuesto) solo de las bondades del modelo de datos:
- Especificacion y limites del modelo de datos

en este otro analisis comentan que "hay algo detras de tu libro" que puede rebasar tus expectativas de analisis:
- Informacion sobre el modelo de datos

en otro articulo comentan formas de "tomar ventaja" del modelo de datos desde Power Pivot:
- El modelo de datos en Power Pivot

pero... viste la diferencia con solo PQ ?: a solo 23Kb en vez de los 117Kb (8 veces mas que el original de 16Kb)
 

Sanlia

New member
Muchas gracias por tu respuesta, pero no es eso exactamente lo que necesito. Lo que necesito es sacar los datos de lo que aparece en la hoja de resultados, sin tener que andar haciendo tablas dinámicas intermedias. Esta tabla la tengo dentro de un modelo de datos con otras muchas cosas, ya en power pivot, y lo que me falta es ser capaz de contar el número de clientes que han comprado en dos años o 3 años fiscales, no uno por uno. Las tablas dinámicas que pongo en mi primera hoja son sólo las intermediarias para calcular el resultado final que aparece en la hoja de resultados, pero quiero evitar hacer estas tablas dinámicas intermediarias, y que en una sola ya pueda tener los datos de la hoja de resultados.

Espero haberme explicado bien.

Gracias de antemano!
 

Héctor Miguel

Well-known member
Lo que necesito es sacar los datos de lo que aparece en la hoja de resultados, sin tener que andar haciendo tablas dinámicas intermedias
(segun yo...) la forma en que necesitas "contar unicos" (de manera directa) tiene algunas "aristas" (p.ej.)

1) para un solo criterio dentro de un listado (completo o para un rango especifico), lo puedes hacer con funciones integradas de excel

2) para un criterio donde haya presencia en dos (o mas) ubicaciones en rangos distintos, necesitas alguna forma de "auxiliares"

3) si pretendes NO usar auxiliares "en rangos o tablas" (probablemente) una alternativa sea el uso de macros o funciones personalizadas (?)
 

Cacho R

Well-known member
Lo que necesito es sacar los datos de lo que aparece en la hoja de resultados...
Hola!
Tu exposición parece bastante clara. A ver:
  • A partir de los datos que tienes en el rango A1 : D41
  • Quieres obtener el resumen de la hoja Resultado
Ahora bien:
  • ¿Siempre vas a comparar 3 años fiscales entre sí?...
  • Te lo pregunto pues haciéndolo obtienes 7 "cuentas de clientes únicos".
  • Pero si fueses a comparar 4 años fiscales pasarías a 15 "cuentas de clientes únicos".
Me parece que deberías "acotar" esta cuestión para definir el método más adecuado a lo que buscas: obtener las combinaciones de "n" elementos tomados de a "m": un tema de combinatoria aplicada, simplemente.

Saludos, Cacho R.
 

Sanlia

New member
Gracias a todos por responder.

Esto lo quiero hacer con power pivot o con DAX, porque tengo un modelo mucho más grande que está todo en power pivot y quiero que quede todo en power pivot para poder automatizar mediante tablas dinámicas mezclando diferentes tablas.

En cuanto si es posible que en algún momento tenga 4 años, efectivamente es posible, mi idea principal es poder contar el número de clientes que han comprado en 3 años fiscales diferentes y partiendo de esa base si necesitara contar los que han comprado en 2 años diferentes o en 4 o 5, poder cambiar la fórmula y hacerlo, pero efectivamente que sea lo más dinámico posible.

Muchas gracias por vuestra ayuda
 

Héctor Miguel

Well-known member
contar el número de clientes que han comprado en 3 años fiscales diferentes y... si necesitara contar... en 2 años diferentes o en 4 o 5, poder cambiar la fórmula... pero efectivamente que sea lo más dinámico posible
este es un avance para contar si se cumplen todas las condiciones, usando:
- funciones integradas (hasta 2 años por la funcion frecuencia, falta investigar por mmult)
- una funcion personalizada (hasta "n" años)

en la UDF estan las bases para los dos restantes (solo en x años o minimo en x años)

si ha de ser (si o si) por Power Pivot (o PQ), lo confirmas ?

nota: la UDF requiere de la libreria de objetos "Microsoft Scripting Runtime"
 

Adjuntos

Última edición:

Sanlia

New member
este es un avance para contar si se cumplen todas las condiciones, usando:
- funciones integradas (hasta 2 años por la funcion frecuencia, falta investigar por mmult)
- una funcion personalizada (hasta "n" años)

en la UDF estan las bases para los dos restantes (solo en x años o minimo en x años)

si ha de ser (si o si) por Power Pivot (o PQ), lo confirmas ?

nota: la UDF requiere de la libreria de objetos "Microsoft Scripting Runtime"

Gracias, pero efectivamente lo necesito hacer con Power Pivot, para así tenerlo integrado en el resto de mi modelo que está todo con Power Pivot.....
 

Héctor Miguel

Well-known member
lo necesito hacer con Power Pivot... integrado en el resto de mi modelo... con Power Pivot
dejo algunas variantes de modelo:
1) con solo PQ (M) cargada a "tabla" (listObject) y a TD
2) agregando una consulta (PQ) al modelo de datos (en preparacion para PP/DAX)
3) un tercer modelo para que analices el uso de funciones personalizadas (vba)
ojo: notar que el IDC0007 lo invente solo para hacer pruebas (en tu modelo original estan como IDC0003)

en los primeros 2, se obtiene (solo) tu primer "tabla auxiliar" (de TD) => unos [1] donde haya compras del cliente por "año fiscal" (cuenta unica)

lo "dificil" es (o pudiera ser) la interpretacion de esos datos para el analisis que pretendes hacer:
a) cuantos (unicos) compraron en el año fiscal "X" ?
b) cuantos (unicos) compraron en los años fiscales "X"-"Y", o "X"-"Z", o "X"-"Y"-"Z", o ?????
c) cuantos (unicos) compraron en todos los años fiscales ?
d) cuantos (unicos) han comprado en (al menos o mas de) "n" años fiscales ?
e) ???

(EMO) la mejor alternativa es por funciones personalizadas (vba), considerando que la intencion es el NO uso de "tablas/rangos auxiliares" (?)
ventaja: es dinamica y la actualizacion es al instante (3er modelo); por PP o PQ o TD se requiere una previa actualizacion

si la condicionante sigue siendo (un "rotundo SI obligado") por DAX, considera lo siguiente:
- la dificultad (en DAX) de crear tablas que NO esten "conectadas" a... (rangos, tablas, ...), razon de la consulta del punto 2
- la necesidad de crear "una medida por cada pregunta" (a, b, c... anteriores), cuantas preguntas tienes contemplado responder ?
- podrias crear medidas con funciones como RELATEDTABLE o DISTINCTOUNT pero habria que hacer "n_anidaciones" (segun preguntas)
- - y ya siendo resultados de un solo dato... como (y donde) los piensas mostrar en tu modelo ?
- el tamaño de los archivos (20 Kb con solo PQ y una de las tablas vs. 185 Kb agregando al modelo de datos) 9+ veces si vas por DAX

esto es solo "para abrir boca", de seguro habra mas (en tanto se da la cuenta de julio/15...)

nos "leemos" la proxima semana ?
 

Adjuntos

Arriba