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

# 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.

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

• ###### Re: Counting users that use combo of two products

Hi,

Please find attached file for solution.

Used User as dimension and this as expression

=Fact(Count(Distinct Product))/(Fact(Count(Distinct Product)- 2) * Fact(2))

Regards,

jagan.

• ###### Re: Counting users that use combo of two products

Thank you so much Jagan!  The dimension needs to be a list of the combination of two products, like "Product A + Product B" or "Product X + Product Y".  Do you think that's possible to do?

Robin

Product Combination        Count Users

Product A + Product B             15

Product X + Product Y              23

Product B + Product Y             17

• ###### Re: Counting users that use combo of two products

Hi,

How many products you have in your data model?

Regards,

jagan.

• ###### Re: Counting users that use combo of two products

Unfortunately there are about 60 products that may be used.

• ###### Re: Counting users that use combo of two products

Hi,

This is very difficult to handle, if products <5 then it can be done, if there are more products it is very difficult but doable.

Regards,

Jagan.

• ###### Re: Counting users that use combo of two products

Might anyone have an idea how this can be done?