Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
try
sum(if(backup_date={"$(=aggr(min({<[Status]={'Close'}>} ,1))
Thank you for ur reply,
I haved tried
sum(if(Data_date={"$(=aggr(min({<[Status]={'Close'}>} ,1))"},Amount))
but it doesn't work
looks like all you need count({<status={'close'}>}status)
by selecting date
can you share qvf
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
;
Thanks
Thank you.
It is my bad. I will do it next time.