topic Re: Rank Count in QlikView

Rank Count

lbunnell — Mon, 16 Jul 2018 15:36:47 GMT

I'd like to use an expression in a text box to give me the 2nd most frequently bought product when a particular product is bought first. I'd also like to get a count of that 2nd product type when it's rank is 2nd and a percentage of the total 2nd products that 2nd Product type represents. How would I modify this expression to get the count rather than just the name of the 2nd Product?

aggr(if(rank( count(distinct{< [1st Product]={'Apples'},AggrCount={'>1'},[Account Number]=P([2ndProductKey]),[2nd Product Date]=P([Open Date]), [1st Product Date]=P([Open Date])>}[Customer ID]))=1,[2nd Product]),[2nd Product]

The point of the exercise is to determine the product path that most customers use (e.g. people who first buy apples, next buy bananas, then they buy oranges - in that order).

Thanks!

Re: Rank Count

vishsaggi — Mon, 16 Jul 2018 15:39:43 GMT

Try this?

= Sum(aggr(if(rank( count(distinct{< [1st Product]={'Apples'},AggrCount={'>1'},[Account Number]=P([2ndProductKey]),[2nd Product Date]=P([Open Date]), [1st Product Date]=P([Open Date])>}[Customer ID]))=1,[2nd Product]),[2nd Product])

Re: Rank Count

lbunnell — Mon, 16 Jul 2018 16:08:54 GMT

I tried it like this

and get "Error in Expression: If takes 2-3 parameters"

Re: Rank Count

vishsaggi — Mon, 16 Jul 2018 16:15:02 GMT

Check this?

= Sum(aggr(if(rank( count(distinct{< [1st Product]={'Apples'},

AggrCount={'>1'},

[Account Number]=P([2ndProductKey]),

[2nd Product Date]=P([Open Date]),

[1st Product Date]=P([Open Date])

>}[Customer ID]))=1,[2nd Product]),[2nd Product]))

Re: Rank Count

lbunnell — Mon, 16 Jul 2018 18:10:25 GMT

Perhaps my original expression is incorrect, but that gives me a count of 0. I'll work on it some more and let you know.

Here's an example of what the table looks like

[Customer ID]	[Product Type]	[Open Date]	[Account Number]	AggrCount	[1st Product]	1stProductKey	[1st Product Date]	[2nd Product]	2ndProductKey	[2nd Product Date]	[3rd Product]	3rdProductKey
1	Apples	5/6/2018	123456	3	Apples	123456	5/6/2018	Oranges	123457	5/7/2018	Bananas	123458
1	Oranges	5/7/2018	123457	3	Apples	123456	5/6/2018	Oranges	123457	5/7/2018	Bananas	123458
1	Bananas	5/10/2018	123458	3	Apples	123456	5/6/2018	Oranges	123457	5/7/2018	Bananas	123458
2	Apples	4/12/2018	234567	2	Apples	234567	4/12/2018	Bananas	234568	4/30/2018
2	Bananas	4/30/2018	234568	2	Apples	234567	4/12/2018	Bananas	234568	4/30/2018
3	Oranges	5/2/2018	345678	3	Oranges	345678	5/2/2018	Apples	345679	6/15/2018	Oranges	345680
3	Apples	6/15/2018	345679	3	Oranges	345678	5/2/2018	Apples	345679	6/15/2018	Oranges	345680
3	Oranges	6/16/2018	345680	3	Oranges	345678	5/2/2018	Apples	345679	6/15/2018	Oranges	345680
4	Apples	7/2/2018	456789	2	Apples	456789	7/2/2018	Oranges	456790	7/3/2018
4	Oranges	7/3/2018	456790	2	Apples	456789	7/2/2018	Oranges	456790	7/3/2018
...

So for the expression above, I would expect a count of 2 customers for the highest ranking 2nd Product, Oranges

Re: Rank Count

vishsaggi — Mon, 16 Jul 2018 18:26:28 GMT

Where is P([2nd Product Key]) in set analysis for Account number?

Re: Rank Count

lbunnell — Mon, 16 Jul 2018 18:49:29 GMT

Sorry about that. I've updated the table above.

Re: Rank Count

vishsaggi — Mon, 16 Jul 2018 19:56:57 GMT

Can you share the app you are working on?

Re: Rank Count

lbunnell — Mon, 16 Jul 2018 20:01:37 GMT

Unfortunately, no. The QVW is rather large and has data that cannot be shared.

Re: Rank Count

vishsaggi — Mon, 16 Jul 2018 20:13:42 GMT

Ok i cannot replicate coz your sample data does not have 1st prod date and 2nd prod dates can you atleast send some sample data with all the respective fields to be used in the expression.

Re: Rank Count

lbunnell — Mon, 16 Jul 2018 20:36:40 GMT

I have added those now as well.

Re: Rank Count

vishsaggi — Tue, 17 Jul 2018 02:48:36 GMT

Will look into this tomorrow.