Count function

[theaokii]

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.

[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))))

[theaokii]

Hi Sunny,

Thank you alot.

Unfortunately,your expression is quite difficult for me to understand.

I have tested but the result doesnt meet the question

There are only 2 ppl whom are 001 and 005 have "Transing" status in Aug.

//im using qliksense server

Could you take a look again.

Thank you

[sunny_talwar]

Why is 002 not included for August?

Is there a reason for 002 to be not included in August? Anyways, try this expression

=Sum(Aggr(If(Only({<[Trans status] = {'Transing'}>}[Cus ID]) = Above(Only({<[Trans status] = {'Transing'}>}[Cus ID])), 0, 1), [Cus ID], (MonthYear, (NUMERIC))))

[theaokii]

Thank you Sunny,

The cus 002 not include in Aug because the 2nd time he bought that us 15/09/2018 meaning he is back:

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

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

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

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

Generally, i gonna count the number of Cus who never order anything again after the first month they order as 001, 003, 004 and 005.

The number of cus in Aug still is 3:

i have stuck to it for a week

// Sometimes, the transaction of customer is like that:

     30-07-2018, 006, 006A, Transing

     31-07-2018, 006, 006A, Transing

     01-08-2018, 006, 006A, Transing

     02-08-2018, 006, 006A, Transing

In that case, i assume that transaction that is in 2018-Jul because the first day (min day of Transing) of transaction is 30-07-2018.

[sunny_talwar]

So, you want to look at customer who have never ever made an order?

[theaokii]

Yeah, I am looking for the customer who have never made an order again.

[sunny_talwar · Accepted solution]

How about this

=Sum(Aggr(If(Only({<[Cus ID] = E({<[Trans status] = {'Ordering'}>})>} [Cus ID]) = Above(Only({<[Cus ID] = E({<[Trans status] = {'Ordering'}>})>} [Cus ID])), 0, 1), [Cus ID], (MonthYear, (NUMERIC))))

[theaokii]

Hi Sunny,

Thank you alot

But it doesnt work .

The result likes above results.

[sunny_talwar]

The result likes above results.

I don't understand? What are you saying when you say "The result likes above results"?