Count records per day or week between two dates / Contar registros por dia o por semana entre dos fechas

[#1]

Hi all.

Hopefully a good Samaritan can help me with a question.

How could I do to QlikView to get the number of rows or number of active clients per day or week or month, between two dates?

Let me explain better:

Given that a client is active between the start date (15/02/2015) and end date (31/12/2015) and another for instance is active between 01.07.2015 and 31/08/2015. The result would be

            Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Active:    0    1     1     1     1     1     2     2     1     1     1     1

Receipts:

LOAD

PartnerID, as SaleDate StartDate, EndDate as ExpiryDate, CenterNumber, ProductID

INLINE [

   SaleID, detailID, PartnerID, SaleDate, ExpiryDate, CenterNumber, ProductID

-1750244418,2060860394,02,2015-04-09 06: 18: 57000.08 / 04 / 2016,40,3

-1271832140,1125522248,03,2015-04-09 11: 41: 25000.08 / 04 / 2016,40,3

-1373267532, -497444023,04,2015-04-09 11: 43: 08,000.08 / 04 / 2016,40,3

-1071843151,30378783,05,2015-01-07 08: 54: 20000.07 / 01 / 2016,40,3

-816 094 798, -172 614 865, 06,2015-01-07 09: 46: 21000.07 / 01 / 2016,40,3

-347 852 157, -1270667203, 406,2015-02-11 21: 50: 44000.31 / 01 / 2016,40,3

-263,549,053.78182460, 407,2015-02-11 21: 56: 47000.31 / 01 / 2016,40,3

-634 216 829, -1557534658,408,2015-02-11 22: 02: 07,000.31 / 01 / 2016,40,3

-46346321, -741541124,409,2015-01-07 00: 00: 00,000.31 / 12 / 2015,40,3

-564 174 295, -436854122,1010,2014-07-21 00: 00: 00,000.21 / 07 / 2015,10,11

-1488200905,1958589297,16,2014-04-12 00: 00: 00,000.12 / 04 / 2015,20,11

]

;

Thank you very much in advance.

- - - -

Hola a todos.

A ver si algún buen samaritano me ayuda con una duda.

¿Como podría hacer para en qlikview para obtener el numero de registros o numero de clientes activos por dia o semana o por mes, entre dos fechas?

Me explico mejor:

Teniendo en cuenta que un cliente esta activo entre la fecha de inicio (15/02/2015) y al fecha fin (31/12/2015) y otro por ejemplo esta activo entre el 1/7/2015 y el 3108/2015. El resultado seria

            Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic

Activo:  0       1    1    1      1     1    2    2    1     1      1     1

Recibos:

LOAD

PartnerID,SaleDate as StartDate, ExpiryDate as EndDate, CenterNumber, ProductID

INLINE [

   SaleID,DetailID,PartnerID,SaleDate,ExpiryDate,CenterNumber,ProductID

-1750244418,2060860394,02,2015-04-09 06:18:57.000,08/04/2016,40,3

-1271832140,1125522248,03,2015-04-09 11:41:25.000,08/04/2016,40,3

-1373267532,-497444023,04,2015-04-09 11:43:08.000,08/04/2016,40,3

-1071843151,30378783,05,2015-01-07 08:54:20.000,07/01/2016,40,3

-816094798,-172614865, 06,2015-01-07 09:46:21.000,07/01/2016,40,3

-347852157,-1270667203, 406,2015-02-11 21:50:44.000,31/01/2016,40,3

-263549053,78182460, 407,2015-02-11 21:56:47.000,31/01/2016,40,3

-634216829,-1557534658,408,2015-02-11 22:02:07.000,31/01/2016,40,3

-46346321,-741541124,409,2015-01-07 00:00:00.000,31/12/2015,40,3

-564174295,-436854122,1010,2014-07-21 00:00:00.000,21/07/2015,10,11

-1488200905,1958589297,16,2014-04-12 00:00:00.000,12/04/2015,20,11

]

;

Muchas gracias de antemano.

Note: Message was edited by Community Moderator to include English translation as a courtesy. Please post in English while in main forums. You may post using other languages in country-based and language-based groups.

[#2]

Good afternoon, he could solve it?

It does not seem at all simple, I was thinking a bit but I could not solve it.

You are using a time dimension? How it is made?

I guess you could solve using Set Analysis playing a third date (the date of the dimension).

Regards.

- - - -

Buenas tardes, pudo resolverlo ?

No parece nada sencillo, estuve pensándolo un poco pero no logré resolverlo.

Está utilizando alguna dimensión de tiempo ? Cómo está compuesta ?

Supongo que se puede resolver aplicando Set Analysis jugando con una tercera FECHA (la fecha de la dimensión).

Saludos.

Note: Message was edited by Community Moderator to include English translation as a courtesy. Please post in English while in main forums. You may post using other languages in country-based and language-based groups.

[maxgro]

You can create all the dates between start date and end date.

Here an example

Creating Reference Dates for Intervals

[#4]

Good evening,

No, I have not yet solved but I'm working on a solution, based on creating a master calendar and combine it with SetAnalysis. I hope to be lucky enough to work me.

I will publish my progress here.

But thank you very much for trying.

Regards

[#5]

I will try. Informs you shortly.

Thank you.

Regards

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

Recibos:
LOAD PartnerID,
    DayName(Timestamp#(SaleDate,'YYYY-MM-DD hh:mm:ss.fff')) as StartDate,
    Date#(ExpiryDate,'DD/MM/YYYY') as EndDate,
    CenterNumber,
    ProductID
Inline [
  SaleID,DetailID,PartnerID,SaleDate,ExpiryDate,CenterNumber,ProductID
-1750244418,2060860394,02,2015-04-09 06:18:57.000,08/04/2016,40,3
-1271832140,1125522248,03,2015-04-09 11:41:25.000,08/04/2016,40,3
-1373267532,-497444023,04,2015-04-09 11:43:08.000,08/04/2016,40,3
-1071843151,30378783,05,2015-01-07 08:54:20.000,07/01/2016,40,3
-816094798,-172614865, 06,2015-01-07 09:46:21.000,07/01/2016,40,3
-347852157,-1270667203, 406,2015-02-11 21:50:44.000,31/01/2016,40,3
-263549053,78182460, 407,2015-02-11 21:56:47.000,31/01/2016,40,3
-634216829,-1557534658,408,2015-02-11 22:02:07.000,31/01/2016,40,3
-46346321,-741541124,409,2015-01-07 00:00:00.000,31/12/2015,40,3
-564174295,-436854122,1010,2014-07-21 00:00:00.000,21/07/2015,10,11
-1488200905,1958589297,16,2014-04-12 00:00:00.000,12/04/2015,20,11
];

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    Date(MonthName(Date),'MM/YY') as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(StartDate) as MinDate, 
    Max(EndDate) as MaxDate 
Resident Recibos;

tabLink:
IntervalMatch(Date)
LOAD Distinct StartDate, EndDate
Resident Recibos;

hope this helps

regards

Marco

[#7]

Thank you so much Marco.

You've been a great help. Your solution worked for me perfectly.

Regards

[#8]

Thank you very much Julio. Your reply has helped me because he has guided me in the right direction.

Finally I used the soluicion Marco has shown me to be more simple, but yours also works.

Thank you.