Hi all,
I have a list of users and the products they are using over a period of time. I need to build a chart that counts the number of users that use a combination of two products that adjusts to the date range/values that the user chooses. A user may use three different products in a time period, in which case they'd be counted as one user using a combination of Product A and Product B, as well as one user using a combination of Product A and Product C, and well as one using Prod B and Prod C. A combination of Prod B and Prod A would a duplicate of Prod A and Prod B. I can't use a combination of more than two products at a time. I used the following expression for the dimension in the first version of my chart and the user said "no, I want to see combos of only two products":
= if(aggr(count(distinct [Product ID]),[User ID]) > 1,aggr(concat(distinct [Product ID],' + '),[User ID]))
My thinking is that I need to create a field that is a list of all the combinations of two products to use as a dimension, and then count the number of users that use them as an expression, I'm just not sure how to construct it. I thought of creating a cartesian join between two lists of the tables but I get the duplicates described above. And I'm not sure how to associate the users and date fields to it.
Data:
User 1, Product A
User 1, Product B
User 1, Product C
User 2, Product A
User 2, Product C
User 2, Product D
Please let me know what you think the correct solution for the script and data model would be. Somehow I imagine once I get that done the chart will be easy, but maybe not.
Thanks very much!
Robin
.png "Luminary Alumni"){kind=icon}