Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
Im gonna count the number of customer whose all transactions start in only 1 month (who never order again after the ordered month) as:
Time | Count number of left customer |
2018-Jul | 1 (003) |
2018-Aug | 2 (001, 005) |
2018-Sep | 1 (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.
It is fine. I just change your Above to Below and done.
Your expression is awesome.
Thank you very much
You saved me once.
i choose showing in August because August is the last transaction day, after that 009 doesnt make any order.
Sounds good. I am glad it worked for you
Yeah. It is great weekend because of you.
Have nice weekend Sunny.