Hello everyone!
I have a report on my company's sales, which in this case are contracts sold to customers. What this report shows is according to the dates in which the contract lasts, how many leads the client receives. In order to be able to see throughout the months of duration that fulfillment we are giving to client.
The problem is as follows:
This information is shown in a table, with their respective calculations, in the form of a traffic light. We have a filter of date up by year and by month (date request) to see which fulfillment we are giving for example every month. What happens? That if a month, a client has not generated leads for whatever reason and I filter for that month, the client, I disappear from my list.
What I would need is that this client does not disappear from the list, but only continues to show me with a value of 0.
This does not happen, since my date is the date of the request, and the client has not received requests therefore has no date.
I have created a master calendar as follows:
MinMaxTable:
Load
min(date) as DateMin,
max(date) as DateMax
Resident RESULTS_IT;
Let vMin = num(Peek('DateMin',0,'TableMinMax'));
Let vMax = num(Peek('MaxDate',0,'MinMaxTable'));
CalendarMaster:
Load
Date(IterNo()+$(vMin)-1) as date
AutoGenerate 1 While $(vMin)+IterNo()-1<=$(vMax);
Drop table MinMaxTable;
Let vMin;
Let vMax;
But since I don't have records, I still can't solve the problem.
I think what I would need is to create records, but I can't make it work this way.
TableMinMax:
Load
min(date) as DateMin,
max(date) as DateMax
Resident RESULTS_IT;
Let vMin = num(Peek('DateMin',0,'TableMinMax'));
Let vMax = num(Peek('MaxDate',0,'MinMaxTable'));
CalendarMaster:
Load
Date(IterNo()+$(vMin)-1) as date
AutoGenerate 1 While $(vMin)+IterNo()-1<=$(vMax);
Drop table MinMaxTable;
Let vMin;
Let vMax;
for vIndex = 1 to NoOfRows('MasterCalendar')
LET vCom = Peek ('com_id',vIndex-1,'RESULTS_IT');
LET date = Peek ('date',vIndex-1,'MasterCalendar');
LET vreq = Peek ('request',vIndex-1,'RESULTS_IT');
Calendar2:
load
'$(vCom)' as com_id,
$(vfecha)' as of date,
If('$(vreq)')='Null',0,'$(vreq)') as newreq;
;
next;
What do you recommend?
Translated with www.DeepL.com/Translator
------
Hola a tod@s!
Tengo un informe sobre las ventas de mi empresa, que en este caso son contratos vendidos a clientes. Lo que muestra este informe es según las fechas en las que dura el contrato, cuantos leads recibe el cliente. Para poder ver así a lo largo de los meses de duración que cumplimiento estamos dando a cliente.
El problema es el siguiente:
Esta información la mostramos en una tabla, con sus respectivos cálculos, en forma de semáforo. Tenemos un filtro de fecha arriba por año y por mes(fecha request) para ver que cumplimiento estamos dando por ejemplo cada mes. Que pasa? Que si un mes, un cliente no ha generado leads por el motivo que sea y yo filtro por ese mes, el cliente, me desaparece de mi listado.
Lo que necesitaría es que este cliente, no desaparezca del listado, sino que únicamente me lo siga mostrando con valor 0.
Esto no sucede, ya que mi fecha es la fecha de la request, y el cliente no ha recibido requests por lo tanto no tiene fecha.
He creado un calendario maestro de la siguiente forma:
TablaMinMax:
Load
min(fecha) as FechaMin,
max(fecha) as FechaMax
Resident RESULTS_IT;
Let vMin = num(Peek('FechaMin',0,'TablaMinMax'));
Let vMax = num(Peek('FechaMax',0,'TablaMinMax'));
CalendarioMaestro:
Load
Date(IterNo()+$(vMin)-1) as fecha
AutoGenerate 1 While $(vMin)+IterNo()-1<=$(vMax);
Drop table TablaMinMax;
Let vMin;
Let vMax;
Pero al no tener registros, sigue sin solucionarme el problema.
Creo que lo que necesitaria es crear registros, pero no consigo hacer que funcione de esta manera
TablaMinMax:
Load
min(fecha) as FechaMin,
max(fecha) as FechaMax
Resident RESULTS_IT;
Let vMin = num(Peek('FechaMin',0,'TablaMinMax'));
Let vMax = num(Peek('FechaMax',0,'TablaMinMax'));
CalendarioMaestro:
Load
Date(IterNo()+$(vMin)-1) as fecha
AutoGenerate 1 While $(vMin)+IterNo()-1<=$(vMax);
Drop table TablaMinMax;
Let vMin;
Let vMax;
for vIndex = 1 to NoOfRows('CalendarioMaestro')
LET vCom = Peek ('com_id',vIndex-1,'RESULTS_IT');
LET vfecha = Peek ('fecha',vIndex-1,'CalendarioMaestro');
LET vreq = Peek ('request',vIndex-1,'RESULTS_IT');
Calendario2:
load
'$(vCom)' as com_id,
'$(vfecha)' as fecha,
If(('$(vreq)')='Null',0,'$(vreq)') as newreq;
;
next;
Que me recomendáis??
