topic Re: Calculating Count in QlikView

Calculating Count

md_qlikview — Thu, 10 Sep 2015 17:29:40 GMT

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

Re: Calculating Count

sunny_talwar — Thu, 10 Sep 2015 17:38:06 GMT

Can you share some sample data with expected output?

Re: Calculating Count

md_qlikview — Thu, 10 Sep 2015 17:46:37 GMT

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?

Re: Calculating Count

sunny_talwar — Thu, 10 Sep 2015 18:55:37 GMT

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)

Re: Calculating Count

Anonymous — Thu, 10 Sep 2015 19:07:06 GMT

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.

Re: Calculating Count

md_qlikview — Thu, 10 Sep 2015 19:08:50 GMT

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

thanks much...

Re: Calculating Count

md_qlikview — Thu, 10 Sep 2015 19:09:51 GMT

Sure. Lemme check it.

thanks much

Re: Calculating Count

sunny_talwar — Thu, 10 Sep 2015 19:13:22 GMT

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:

Re: Calculating Count

maxgro — Thu, 10 Sep 2015 19:19:51 GMT

try with a str table with

dimension Mode

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

Re: Calculating Count

md_qlikview — Fri, 11 Sep 2015 06:10:34 GMT

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

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 06:13:34 GMT

You mean if for a minimum date there are 2 Modes of transaction: For example, Cash and Check?

Re: Calculating Count

md_qlikview — Fri, 11 Sep 2015 06:14:29 GMT

yes, in that case it is giving me null and hence count of customer will be ultimately null

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 06:16:40 GMT

In that case which one would you like it to be counted towards? Cash or Check or both?

Re: Calculating Count

md_qlikview — Fri, 11 Sep 2015 06:22:11 GMT

As of now, i am not clear about it. But what if i am having multiple transactions but of the same type eg. Cash then how will i will choose one.

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 06:24:50 GMT

Then you can use this:

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

It will show your syntax error, but the expression should work.

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 06:29:21 GMT

Check this:

Re: Calculating Count

md_qlikview — Fri, 11 Sep 2015 06:42:51 GMT

Yes, it is working. I have to check this with multiple scenarios.

Thanks much

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 06:46:25 GMT

No problem

I am glad we were able to help you here. I know you are still checking, but I would suggest marking correct answers and Helpful answers once you have your finalized solution

Best,

Sunny

Re: Calculating Count

md_qlikview — Fri, 11 Sep 2015 07:51:15 GMT

Thanks a lot everyone who has chipped in, and shown your sincere efforts. I got it required output.

Thanks much.

Re: Calculating Count

sunny_talwar — Fri, 11 Sep 2015 07:55:00 GMT

Awesome