Count number of close account per date

[Anonymous]

Hi everyone,

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

There is my raw data:

Acc ID	Status	Amount	Data_date
10001	Open	100	2/5/2018
10001	Open	100	3/5/2018
10001	Close	0	4/5/2018
10002	Close	0	2/5/2108
10002	Close	0	3/5/2018
10002	Close	0	4/5/2018
10003	Open	1200	2/5/2018
10003	Open	1200	3/5/2018
10003	Close	0	4/5/2018
10005	Open	500	2/5/2018
10005	Close	0	3/5/2018
10005	Close	0	4/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 ID	Day Close	Amount before Close
10001	4/5/2018	100
10002	2/5/2018	Null
10003	4/5/2018	1200
10005	3/5/2018	500

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

Data_date	Number of Close	Amount before Close
2/5/2018	1	Null
3/5/2018	1	500
4/5/2018	2	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. ; 
4. LOAD * Inline 
5. [ 
6.   'Acc ID', 'Status', 'Amount', 'Data_date_text', 
7.   '10001', 'Open', '100', '2/5/2018' 
8.   '10001', 'Open', '100', '3/5/2018' 
9.   '10001', 'Close', '0', '4/5/2018' 
10.   '10002', 'Close', '0', '2/5/2108' 
11.   '10002', 'Close', '0', '3/5/2018' 
12.   '10002', 'Close', '0', '4/5/2018' 
13.   '10003', 'Open', '1200', '2/5/2018' 
14.   '10003', 'Open', '1200', '3/5/2018' 
15.   '10003', 'Close', '0', '4/5/2018' 
16.   '10005', 'Open', '500', '2/5/2018' 
17.   '10005', 'Close', '0', '3/5/2018' 
18.   '10005', 'Close', '0', '4/5/2018' 
19. ] 
20. ; 
21.  
22.  
23. DROP FIELD Data_date_text 
24. ; 

[zebhashmi]

try

sum(if(backup_date={"$(=aggr(min({<[Status]={'Close'}>} ,1))

[Anonymous]

Thank you for ur reply,

I haved tried

sum(if(Data_date={"$(=aggr(min({<[Status]={'Close'}>} ,1))"},Amount))

but it doesn't work

[zebhashmi]

looks like all you need count({<status={'close'}>}status)

by selecting date

can you share qvf

[JustinDallas]

I find that it always helps to have a simple copy-and-paste example at the ready.  People are more apt to give your problem a crack.

DummyData:
LOAD *, Date(DATE#(Data_date_text,'M/D/YYYY')) AS Data_date 
;
LOAD * Inline
[
  'Acc ID', 'Status', 'Amount', 'Data_date_text',
  '10001', 'Open', '100', '2/5/2018'
  '10001', 'Open', '100', '3/5/2018'
  '10001', 'Close', '0', '4/5/2018'
  '10002', 'Close', '0', '2/5/2108'
  '10002', 'Close', '0', '3/5/2018'
  '10002', 'Close', '0', '4/5/2018'
  '10003', 'Open', '1200', '2/5/2018'
  '10003', 'Open', '1200', '3/5/2018'
  '10003', 'Close', '0', '4/5/2018'
  '10005', 'Open', '500', '2/5/2018'
  '10005', 'Close', '0', '3/5/2018'
  '10005', 'Close', '0', '4/5/2018'
]
;


DROP FIELD Data_date_text
;

[zebhashmi]

Thanks

[zebhashmi]

[Anonymous]

Thank you.

It is my bad. I will do it next time.