22 Replies Latest reply: Nov 9, 2018 3:25 PM by Aoki Bi RSS

    Count function

    Aoki Bi

      Hi guys,

       

      Im new to Qlik Sense. And yet, i have met the difficulty about Count function

      There is my table data about transactions of customers:

        

      DayCus IDTrans IDTrans status
      15-08-2018001001ATransing
      16-08-2018001001ATransing
      17-08-2018001001ATransing
      18-08-2018001001ATransing
      16-08-2018001001BTransing
      17-08-2018001001BTransing
      18-08-2018001001BTransing
      15-09-2018002002AOrdering
      16-09-2018002002AOrdering
      17-09-2018002002AOrdering
      18-09-2018002002AOrdering
      16-08-2018002002BTransing
      17-08-2018002002BTransing
      18-08-2018002002BTransing
      15-07-2018003003ATransing
      16-07-2018003003ATransing
      17-08-2018003003ATransing
      17-09-2018004004ATransing
      18-09-2018004004ATransing
      02-08-2018005005ATransing
      03-08-2018005005ATransing
      04-08-2018005005ATransing

       

      Im gonna count the number of customer whose all transactions start in only 1 month (who never order again after the ordered month) as:

       

      TimeCount number of left customer
      2018-Jul1 (003)
      2018-Aug2 (001, 005)
      2018-Sep1 (004)

       

      In my case, i assume that min({<Transtatus={'Transing'}>} day) is the start day of transaction

      Is it possible to make it or may a data table?

       

      There is the sql data

      Data:
      Load * Inline
      [
        
      
      
      
      'Day','Cus ID','Trans ID','Trans status'
      
      
      '15-08-2018','001','001A','Transing'
      
      
      '16-08-2018','001','001A','Transing'
      
      
      '17-08-2018','001','001A','Transing'
      
      
      '18-08-2018','001','001A','Transing'
      
      
      '16-08-2018','001','001B','Transing'
      
      
      '17-08-2018','001','001B','Transing'
      
      
      '18-08-2018','001','001B','Transing'
      
      
      '15-09-2018','002','002A','Ordering'
      
      
      '16-09-2018','002','002A','Ordering'
      
      
      '17-09-2018','002','002A','Ordering'
      
      
      '18-09-2018','002','002A','Ordering'
      
      
      '16-08-2018','002','002B','Transing'
      
      
      '17-08-2018','002','002B','Transing'
      
      
      '18-08-2018','002','002B','Transing'
      
      
      '15-07-2018','003','003A','Transing'
      
      
      '16-07-2018','003','003A','Transing'
      
      
      '17-08-2018','003','003A','Transing'
      
      
      '17-09-2018','004','004A','Transing'
      
      
      '18-09-2018','004','004A','Transing'
      
      
      '02-08-2018','005','005A','Transing'
      
      
      '03-08-2018','005','005A','Transing'
      
      
      '04-08-2018','005','005A','Transing'
      
      
      
      
      ]
      ;
      

      Could anyone enlight me!

       

      Thanks in advance.

        • Re: Count function
          Sunny Talwar

          Try this

           

          Data:

          LOAD *,

            Month(Day) as Month,

            Date(MonthStart(Day)) as MonthYear;

          LOAD * INLINE [

              Day, Cus ID, Trans ID, Trans status

              15-08-2018, 001, 001A, Transing

              16-08-2018, 001, 001A, Transing

              17-08-2018, 001, 001A, Transing

              18-08-2018, 001, 001A, Transing

              16-08-2018, 001, 001B, Transing

              17-08-2018, 001, 001B, Transing

              18-08-2018, 001, 001B, Transing

              15-09-2018, 002, 002A, Ordering

              16-09-2018, 002, 002A, Ordering

              17-09-2018, 002, 002A, Ordering

              18-09-2018, 002, 002A, Ordering

              16-08-2018, 002, 002B, Transing

              17-08-2018, 002, 002B, Transing

              18-08-2018, 002, 002B, Transing

              15-07-2018, 003, 003A, Transing

              16-07-2018, 003, 003A, Transing

              17-08-2018, 003, 003A, Transing

              17-09-2018, 004, 004A, Transing

              18-09-2018, 004, 004A, Transing

              02-08-2018, 005, 005A, Transing

              03-08-2018, 005, 005A, Transing

              04-08-2018, 005, 005A, Transing

          ];

           

          Expression

          =Sum(Aggr(If([Cus ID] = Above([Cus ID]), 0, 1), [Cus ID], (MonthYear, (NUMERIC))))