Rank Count

[lbunnell]

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!

[vishsaggi]

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

[lbunnell]

I tried it like 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])))

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

[vishsaggi]

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

[lbunnell]

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

[vishsaggi]

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

[lbunnell]

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

[vishsaggi]

Can you share the app you are working on?

[lbunnell]

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

[vishsaggi]

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.