7 Replies Latest reply: Feb 2, 2016 10:43 PM by E. Rivera RSS

    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.