• 👏 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

Asignar facturas a último envío

Sanlia

New member
Buenas tardes,

Llevo días pensando en cómo resolver este problema, pero todas las soluciones que consigo hacen que se me quede bloqueado el ordenador, porque trabajo con un gran volumen de datos. Y estoy convencida de que vosotros podéis ayudarme.

Os cuento el problema. Tengo una tabla con el detalle de facturas, que tiene varias columnas (código factura, código cliente, fecha de factura y ventas €) y otra con el detalle de envíos de sms realizados que tiene también varias columnas (Código envío comunicación, fecha envío comunicación, fecha inicio promoción, fecha fin promoción y código cliente).

En la tabla de facturas, las facturas aparecen una sola vez, pero el código de cliente y la fecha se repiten, porque puede haber un cliente que haga varias facturas y varios clientes que compren en la misma fecha.

En la tabla de envíos de sms, el código de envío de comunicación aparece una soa vez, pero el resto de columnas pueden repetirse, puesto que un cliente puede haber recibido varios envíos, y se pueden hacer varios envíos en la misma fecha que pueden tener las mismas fechas de inicio y fin promo.

Lo que quiero conseguir, es una tabla dinámica que me proporcione la cantidad de clientes únicos que han comprado dentro del período de promoción, la cantidad de facturas realizadas en el período de promoción y el total de las ventas realizadas en el período de promoción, todo ello por cada mes de envío de comunicación. De forma que hay que tener en cuenta que la fecha de la factura debe estar entre la fecha de inicio y fin de la promoción, y que si hay una factura realizada en una fecha que está dentro del período de promoción de dos envíos, quiero que me asigne todos los datos (clientes, facturas y ventas) al último envío, de forma que no me aparezcan duplicados en los dos mes, y sólo estén asignados al último mes de envío (y si varios envíos se han realizado en la misma fecha, que se asigne al código de envío superior, que será el último código de envío). Y sólo se tiene que tener en cuenta si al cliente se le ha enviado la comunicación y luego ha comprado en el período promoción. Es decir, no vale si la factura está dentro del período, pero el cliente no ha recibido comunicación.

Para ello, lo primero que habría que hacer sería identificar si la fecha de factura está dentro del período de promoción del envío que ha recibido el cliente, y después habría que asignar cada factura al envío que la ha producido, pero no sé cómo hacerlo sin que luego se duplique por meses e incluso dentro del mismo mes. En excel, sí sabría hacerlo, pero necesito hacerlo en power pivot con funciones dax, porque al hacerlo en excel se me queda el ordenador bloqueado.

Adjunto un pequeño ejemplo.

Muchas gracias de antemano por vuestra ayuda!!!
Muchas gracias
 

Adjuntos

Héctor Miguel

Well-known member
... si hay una factura realizada en una fecha que está dentro del período de promoción de dos envíos, quiero que me asigne todos los datos (clientes, facturas y ventas) al último envío, de forma que no me aparezcan duplicados en los dos mes, y sólo estén asignados al último mes de envío ...
el caso que expones tiene su grado de complejidad por las restricciones/condiciones que mencionas

- concentrar "por fecha de comunicado" (y clientes únicos) "siempre y cuando"...
- hayan facturado "dentro de" el período de promoción
- "acumulando" importes... "a la fecha del comunicado" ?, o a la fecha "dentro de" la promoción ?
- sin contar doble en los meses traslapados (?)
=> que tiene que ver el mes de promoción y factura con el mes del comunicado ?
=> qué significa "sin duplicar" bajo este contexto ?

serviría si a tu modelo le pones "a mano" en el ejemplo de TD los resultados que esperas obtener
(y que comentes con toda claridad las consideraciones tomadas en cuenta para tres o cuatro de esos resultados)

puedes agregar cómo lo harías "con solo excel"

de seguro tienes algún otro "detalle en el tintero" (?), es buen momento para comentarlo(s)
 

Sanlia

New member
Hola Héctor, gracias por tu respuesta, te contesto a tus preguntas:

-Quiero que la información aparezca por la fecha de envío de comunicación, pero que en ella se engloben los resultados de las facturas realizadas en el período de promoción (normalmente la fecha de envío es un par de días o incluso el mismo día en que comienza la fecha de promo), pero al final lo que quiero ver son las facturas que han generado los envíos.
- Sin contar doble en los meses traslapados, efectivamente, es decir, que si tengo un envío en agosto para una promo que va del 01/08 al 30 de septiembre, y otro envío en septiembre para una promo que va del 01/09 al 30/09, y se lo he enviado al cliente "1", y este cliente compra el 15/09 (cómo estaría dentro de los dos períodos de promoción, quiero que me lo asigne al último envío realizado, es decir, al que he enviado en septiembre, y me ponga los datos en ese mes, y no en el anterior.
- La relación entre las fechas de envío de comunicación, promoción y factura, es que yo hago un envío, para que después en un período de promoción que va desde 1 día a otro, el cliente venga a comprar (que sería la fecha de la factura)
- Sin duplicar, quiere decir, que en el ejemplo que he puesto en el segundo punto, los resultados de la factura sólo aparezcan en el mes de envío de comunicación de septiembre y no me aparezcan también en el de agosto.

Como yo lo haría en excel, no creo que sea una buena forma, pero te cuento. Lo que haría sería combinar ambas tablas en power query, de forma que me saque todas las líneas de la tabla de envíos de comunicación y sólo las facturas que crucen, aquí aparecen duplicadas las facturas, con lo que ordeno por fecha de envío descendente y por factura, y agrego una columna que mire si la factura de la celda anterior es igual a la de la celda en la que estoy, si es igual pongo 0 a las ventas, y facturas, y si no pongo el valor de las ventas € (para clientes tendría que hacer lo mismo pero ordenando de nuevo, en otra tabla, esta vez por cliente en lugar de factura)....

Muchas gracias de nuevo por tu ayuda!!!
 

Héctor Miguel

Well-known member
Como yo lo haría en excel, no creo que sea una buena forma, pero te cuento
del análisis a tu (corta) muestra salen detalles como p.ej.
- a 8 de tus clientes se les enviaron 2 invitaciones que "traslapan" la vigencia de la promoción (o es la misma)
- TODAS tienen la misma fecha de SMS (pero el segundo IDE es posterior)
- en uno de los segundos SMS, al cliente se le "recorta" 1 mes el período de la vigencia
(por fortuna no hizo compras, pero... qué pasa si compra en el mes "recortado" del primer SMS ?)

(creo que) llegué al punto donde "se atora" excel (o PQ ?) y asumo que es por la base y forma con que remueves duplicados (?)
cambiando algunas trasformaciones se resuelve esa situación

sería conveniente que muestres los resultados que TU darías por buenos en la misma muestra de TD de tu adjunto según lo solicitado:
serviría si a tu modelo le pones "a mano" en el ejemplo de TD los resultados que esperas obtener
(y que comentes con toda claridad las consideraciones tomadas en cuenta para tres o cuatro de esos resultados)
serviría para comparar si lo que devuelve PQ (al menos) se parece a lo que pongas "a mano" (con las explicaciones del caso ?)

no quiero pensar (aun) en bases de datos de varias decenas/centenas de miles de registros (hasta tener claro lo anterior)
 

Sanlia

New member
No estoy seguro de haber acertado con todo lo que pides. Pero bueno, ¿pruebas el adjunto?
Muchas gracias por tu respuesta, pero creo que eso no es lo que estoy buscando. He intentado realizar yo los cálculos en el mi excel de ejemplo, aunque de una forma bastante rudimentaria. Lo adjunto aquí para que puedas ver lo que tendría que dar, según mi razonamiento.

La combinación de las tablas de envíos y facturas, la hago en access de forma que salgan todas las líneas de envíos y sólo aquellas que crucen con facturas, de forma que me aparecen los datos con facturas duplicadas, por ello luego creo una columna que me diga si la fecha está dentro y fuera de la promo, y tengo que ordenar de diferentes formas para sacar las ventas € únicas, facturas únicas y clientes únicos (explico como ordeno dentro del fichero). Es una forma muy rudimentaria de hacerlo, y al final el excel pesa muchísimo cuando tengo gran volumen de datos y acaba estropeándose.

Y seguramente haya una forma más eficaz de hacerlo con power pivot-power query.

Muchas gracias
 

Adjuntos

Sanlia

New member
del análisis a tu (corta) muestra salen detalles como p.ej.
- a 8 de tus clientes se les enviaron 2 invitaciones que "traslapan" la vigencia de la promoción (o es la misma)
- TODAS tienen la misma fecha de SMS (pero el segundo IDE es posterior)
- en uno de los segundos SMS, al cliente se le "recorta" 1 mes el período de la vigencia
(por fortuna no hizo compras, pero... qué pasa si compra en el mes "recortado" del primer SMS ?)

(creo que) llegué al punto donde "se atora" excel (o PQ ?) y asumo que es por la base y forma con que remueves duplicados (?)
cambiando algunas trasformaciones se resuelve esa situación

sería conveniente que muestres los resultados que TU darías por buenos en la misma muestra de TD de tu adjunto según lo solicitado:

serviría para comparar si lo que devuelve PQ (al menos) se parece a lo que pongas "a mano" (con las explicaciones del caso ?)

no quiero pensar (aun) en bases de datos de varias decenas/centenas de miles de registros (hasta tener claro lo anterior)
Hola!! Muchas gracias por tu respuesta.

Lo que busco siempre es que la factura de un cliente se asocie al último envío que le he hecho a ese cliente, siempre y cuando la fecha de factura esté dentro del período de promoción.

Adjunto el fichero con el resultado de cómo lo haría yo (pero es muy rudimentario y con gran volumen el excel se queda pillado).

Muchas gracias de nuevo
 

Adjuntos

Héctor Miguel

Well-known member
que la factura de un cliente se asocie al último envío que le he hecho a ese cliente, siempre y cuando la fecha de factura esté dentro del período de promoción.

Adjunto el fichero con el resultado de cómo lo haría yo (pero es muy rudimentario y con gran volumen el excel se queda pillado)
el resultado que obtengo es una mezcla de tu reciente adjunto y lo que devuelve la propuesta de hc
- fecha del sms que genera la venta: igual a tu muestra (los números NO)
- cuenta de clientes, facturas y ventas: igual a lo de @hc3115 (sus fechas no son lo mismo)

creo que en los pasos por access hay "algo" que se está quedando fuera de los análisis (pero cual y como ???)

reduje el largo de tus títulos originales (prefiero los breves pero significativos a los totalmente descriptivos pero excesivamente largos)
en el adjunto (Fx...), todo va "a pura fórmula", por lo que un "refresco" de los cálculos sucede "en tiempo real"
y como verás, no se necesita ni relacionar tablas, ni usar las power, ni usar el modelo de datos, ni macros, ni ... (aunque son alternativas viables)

hoja "sms":
- está ordenada por "codCliente" ascendente
- el formato indica períodos traslapados de la promoción
- columna "G" indica cuantas promociones se enviaron a cada cliente
- columna "H" identifica períodos con traslape de fechas (si >1)
- columna "I" identifica compras "dentro de" los períodos de promoción
- - el formato condicional "descarta" compras de la "primera invitación" (si es el caso)
- columna "J" marca las del último aviso (SMS)
- columna "K" rescata la fecha-mes del SMS que genera la compra
- columna "L" lista los meses (únicos) para comparar con los de la hoja "facturas"
=> columnas "G" a "L" NO son necesarias, son solo para "comprobación visual"

hoja "facturas":
- a la tabla se agregaron dos columnas (E y F)
- columna "E" calcula si la compra corresponde a algún período de promoción de un SMS
- columna "F" calcula cual es el mes del último SMS con el período de promoción de la compra
- columnas I a M son cálculos para "armar" una (pseudo)-Tabla (dinámica) de los resultados
- como va una "preparación en exceso", la casilla en [H1] aplica formato condicional para ocultarlos

en el adjunto (PQ ...) obtengo resultados idénticos usando "M" y de respuesta inmediata
(claro, el volumen de datos de tu muestra ha sido minúsculo)
la diferencia puede ser en el arreglo de la TD, ya que puedes mostrar cuales clientes se están reportando en cada fecha
(en lugar de "solo la cuenta")
 

Adjuntos

Última edición:

Sanlia

New member
Hola Héctor,

Muchísimas gracias por tu respuesta, la he mirado en detalle y tienes razón, mis números no eran correctos, fallaban por temas de formato de fecha y de otra consideración en la cuenta de los clientes únicos. Los datos correctos son los que tú aportas en las dos soluciones.

Con respecto a la primera, a la de excel:
- No consigo seguir la lógica de la fórmula que usas en la columna "J" (compras únicas) de la hoja SMS ¿por qué cuentas de 3 en 3 y sumas los períodos traslapados? Sé que luego no la usas para sacar los resultados, pero me encantaría comprender la lógica que sigues.
- También me pierdo un poco en la fórmula de la columna de clientes únicos (columna K) de la hoja facturas
En cualquier caso, como dices, mis datos reales son mucho más numerosos y el excel no es capaz de calcular todas esas fórmulas. Entonces optaría por usar la segunda solución con power query.

Con respecto a la segunda solución de power query, soy capaz de seguir los pasos de la consulta reporte, hasta la parte de cuenta facturas, veo que llamas a la función que has creado "CSI", pero mi manejo del power query no llega hasta ese punto, es decir no sé cómo se crean funciones personalizadas y como se aplican después y no comprendo qué es lo que hace esa función que has creado. Te agradecería si me pudieras explicar esta parte.

Y de nuevo muchísimas gracias por dedicar tiempo para ayudarme.

Un saludo!
 

Héctor Miguel

Well-known member
la columna J se basa en que la tabla "sms" esta ordenada por "codCliente" asi que basta buscar en el actual {+} los dos siguientes y descartar (de columna H) el traslapado anterior

la columna K es una de las formas de "contar unicos" bajo condiciones, en este caso, los que se reportan por el "mes-sms"

como dije, son solo como una "comprobacion visual" de lo que devuelve ese tipo de calculos

con respecto de la Fn en PQ (CSI), es una programacion para emular la funcion integrada =CONTAR.SI(... en excel
si analizas esa consulta desde el editor avanzado en PQ entenderas mejor los pasos que sigue

en el paso "cuentaFacturas" se requiere del paso previo (combinaMesCliente) para usar una sola "condicion" para ese CONTAR.SI(...
tambien se pudo haber creado otra Fn para emular un CONTAR.SI.CONJUNTO(... (o similares) pero me parecio no necesario
 
Arriba