Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

6 Replies
MVP & Luminary
MVP & Luminary

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.

Not applicable

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

MVP & Luminary
MVP & Luminary

Re: Counting users that use combo of two products

Hi,

How many products you have in your data model?

Regards,

jagan.

Not applicable

Re: Counting users that use combo of two products

Unfortunately there are about 60 products that may be used.

MVP & Luminary
MVP & Luminary

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.

Not applicable

Re: Counting users that use combo of two products

Might anyone have an idea how this can be done?

Community Browser