6 Replies Latest reply: Mar 27, 2014 10:10 AM by Robin Cushman RSS

    Counting users that use combo of two products

      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.



      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!