Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Hi,
How many products you have in your data model?
Regards,
jagan.
Unfortunately there are about 60 products that may be used.
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.
Might anyone have an idea how this can be done?