7 Replies Latest reply: Aug 29, 2018 9:44 PM by aohk iub RSS

    Count number of close account per date

    aohk iub

      Hi everyone,

      Im newbie for Qliksense and haved faced a trouble about counting per day

      There is my raw data:

       

      Acc IDStatusAmountData_date
      10001Open1002/5/2018
      10001Open1003/5/2018
      10001Close04/5/2018
      10002Close02/5/2108
      10002Close03/5/2018
      10002Close04/5/2018
      10003Open12002/5/2018
      10003Open12003/5/2018
      10003Close04/5/2018
      10005Open5002/5/2018
      10005Close03/5/2018
      10005Close04/5/2018

      Data_date is the day that i backup data

      Im using aggr(min({<[Status]={'Close'}>} Data_date),ACC_ID)

      and FirstSortedValue({<[Status]={'Open'}>} Amount,-Data_date) to perform a table of closed AccID

       

      Acc IDDay CloseAmount before Close
      100014/5/2018100
      100022/5/2018Null
      100034/5/20181200
      100053/5/2018500

       

      Hence, now Im tending to count the number of Acc ID had been closed followed by Data_date like that but cant:

       

      Data_dateNumber of CloseAmount before Close
      2/5/20181Null
      3/5/20181500
      4/5/20182

      1300

      Haved tried the measure count({<Data_date={"$(=aggr(min({<[Status]={'Close'}>} Data_date),ACC_ID))"}>} ACC_ID) and Sum but it doesnt work.

      Could anyone help me!

      Thanks in advance.

       

      Thank mr Justin for a mock data:

      1. DummyData: 
      2. LOAD *, Date(DATE#(Data_date_text,'M/D/YYYY')) AS Data_date  
      3. LOAD * Inline 
      4.   'Acc ID', 'Status', 'Amount', 'Data_date_text'
      5.   '10001', 'Open', '100', '2/5/2018' 
      6.   '10001', 'Open', '100', '3/5/2018' 
      7.   '10001', 'Close', '0', '4/5/2018' 
      8.   '10002', 'Close', '0', '2/5/2108' 
      9.   '10002', 'Close', '0', '3/5/2018' 
      10.   '10002', 'Close', '0', '4/5/2018' 
      11.   '10003', 'Open', '1200', '2/5/2018' 
      12.   '10003', 'Open', '1200', '3/5/2018' 
      13.   '10003', 'Close', '0', '4/5/2018' 
      14.   '10005', 'Open', '500', '2/5/2018' 
      15.   '10005', 'Close', '0', '3/5/2018' 
      16.   '10005', 'Close', '0', '4/5/2018' 
      17.  
      18.  
      19. DROP FIELD Data_date_text