Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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.

1 Solution

Accepted Solutions

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

Hi,

one solution could be:

QlikCommunity_Thread_203629_Pic1.JPG

QlikCommunity_Thread_203629_Pic2.JPG

QlikCommunity_Thread_203629_Pic3.JPG

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 Replies
Not applicable

Re: Contar registros por dia o por semana entre dos fechas

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.

MVP
MVP

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

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

Here an example

Creating Reference Dates for Intervals

Not applicable

Re: Contar registros por dia o por semana entre dos fechas

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

Not applicable

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

I will try. Informs you shortly.

Thank you.

Regards

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

Hi,

one solution could be:

QlikCommunity_Thread_203629_Pic1.JPG

QlikCommunity_Thread_203629_Pic2.JPG

QlikCommunity_Thread_203629_Pic3.JPG

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

Not applicable

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

Thank you so much Marco.

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


Regards

Not applicable

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

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.

Community Browser