Calculating Count

[md_qlikview]

I am having a scenario where I have customers who has done some transactions by purchasing some goods. One customer is having multiple transactions and each transaction is also having date and mode of transaction.

Now, I want to calculate no of customers for each mode of transactions who has used that mode of transactions as his very first transaction. First transaction may be decided by the date of transaction.

Thanks In Advance

[sunny_talwar]

Can you share some sample data with expected output?

[md_qlikview]

I would love to bt i wont be able to share it due to some security reasons. I can elaborate a bit more like,

eg. For a particular customer  say X who has done total 10 transactions by using different modes like Cash, Credit card etc. For him what was the mode of his first transaction that i need to find, which can be calculated by taking date of his first transation, by min(date). Likewise I have to calculate for each mode how many customers who has chosen that mode for their first transaction.

Is it possible using Set experession?

[sunny_talwar]

Try this sample:

Script:

Table:

LOAD * Inline [

Customer, Date, Mode

A, 01/01/2015, Cash

A, 02/01/2015, CreditCard

A, 03/01/2015, Cash

A, 04/01/2015, Check

B, 02/01/2015, Check

B, 03/01/2015, Cash

B, 04/01/2015, Check

C, 03/01/2015, CreditCard

C, 04/01/2015, Check

];

Screenshot of Data and the required chart

Expression:

=FirstSortedValue(Mode, Date)

[Anonymous]

Based on your requirement I created a sample data set and app wherein I counted what you asked for. Please go over. There might be other better way to do it as well.

[md_qlikview]

thanks a lot sunny. Appreciated your sincere efforts. I will look into this and will let you know on this.

thanks much...

[md_qlikview]

Sure. Lemme check it.

thanks much

[sunny_talwar]

For doing the count, try this expression:

=Count(Aggr(FirstSortedValue(Mode, Date), Customer))

With Mode as Dimension

New Script:

Table:

LOAD * Inline [

Customer, Date, Mode

A, 01/01/2015, Cash

A, 02/01/2015, CreditCard

A, 03/01/2015, Cash

A, 04/01/2015, Check

B, 02/01/2015, Check

B, 03/01/2015, Cash

B, 04/01/2015, Check

C, 03/01/2015, CreditCard

C, 04/01/2015, Check

D, 03/01/2015, Cash

D, 04/01/2015, Check

];

Output:

[maxgro]

try with a str table with

dimension     Mode

expression     count(aggr(FirstSortedValue(Mode, Date),Customer))

[md_qlikview]

Hi Massimo,

it is giving me correct output. But how can i handle null value, since If more than one value of expression share the same lowest sort-order, the function will return NULL