Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
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"
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]))
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
Where is P([2nd Product Key]) in set analysis for Account number?
Sorry about that. I've updated the table above.
Can you share the app you are working on?
Unfortunately, no. The QVW is rather large and has data that cannot be shared.
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.