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!
