Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

theaokii
New Contributor II

Count function

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:

  

DayCus IDTrans IDTrans status
15-08-2018001001ATransing
16-08-2018001001ATransing
17-08-2018001001ATransing
18-08-2018001001ATransing
16-08-2018001001BTransing
17-08-2018001001BTransing
18-08-2018001001BTransing
15-09-2018002002AOrdering
16-09-2018002002AOrdering
17-09-2018002002AOrdering
18-09-2018002002AOrdering
16-08-2018002002BTransing
17-08-2018002002BTransing
18-08-2018002002BTransing
15-07-2018003003ATransing
16-07-2018003003ATransing
17-08-2018003003ATransing
17-09-2018004004ATransing
18-09-2018004004ATransing
02-08-2018005005ATransing
03-08-2018005005ATransing
04-08-2018005005ATransing

Im gonna count the number of customer whose all transactions start in only 1 month (who never order again after the ordered month) as:

TimeCount number of left customer
2018-Jul1 (003)
2018-Aug2 (001, 005)
2018-Sep1 (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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Count function

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

22 Replies
MVP
MVP

Re: Count function

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
New Contributor II

Re: Count function

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

MVP
MVP

Re: Count function

Why is 002 not included for August?

Capture.PNG

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
New Contributor II

Re: Count function

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.

MVP
MVP

Re: Count function

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

theaokii
New Contributor II

Re: Count function

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

MVP
MVP

Re: Count function

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
New Contributor II

Re: Count function

Hi Sunny,

Thank you alot

But it doesnt work .

The result likes above results.

MVP
MVP

Re: Count function

The result likes above results.

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