Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lbunnell
Creator
Creator

Rank Count

I'd like to use an expression in a text box to give me the 2nd most frequently bought product when a particular product is bought first. I'd also like to get a count of that 2nd product type when it's rank is 2nd and a percentage of the total 2nd products that 2nd Product type represents. How would I modify this expression to get the count rather than just the name of the 2nd Product?

aggr(if(rank( count(distinct{< [1st Product]={'Apples'},AggrCount={'>1'},[Account Number]=P([2ndProductKey]),[2nd Product Date]=P([Open Date]), [1st Product Date]=P([Open Date])>}[Customer ID]))=1,[2nd Product]),[2nd Product]

The point of the exercise is to determine the product path that most customers use (e.g. people who first buy apples, next buy bananas, then they buy oranges - in that order).

Thanks!

11 Replies
vishsaggi
Champion III
Champion III

Try this?

= Sum(aggr(if(rank( count(distinct{< [1st Product]={'Apples'},AggrCount={'>1'},[Account Number]=P([2ndProductKey]),[2nd Product Date]=P([Open Date]), [1st Product Date]=P([Open Date])>}[Customer ID]))=1,[2nd Product]),[2nd Product])

lbunnell
Creator
Creator
Author

I tried it like this

= Sum(aggr(if(rank( count(distinct{< [1st Product]={'Apples'},AggrCount={'>1'},[Account Number]=P([2ndProductKey]),[2nd Product Date]=P([Open Date]), [1st Product Date]=P([Open Date])>}[Customer ID]))=1,[2nd Product]),[2nd Product])))

and get "Error in Expression: If takes 2-3 parameters"

vishsaggi
Champion III
Champion III

Check this?

= Sum(aggr(if(rank( count(distinct{< [1st Product]={'Apples'},

                                     AggrCount={'>1'},

                                     [Account Number]=P([2ndProductKey]),

                                     [2nd Product Date]=P([Open Date]),

                                     [1st Product Date]=P([Open Date])

                                   >}[Customer ID]))=1,[2nd Product]),[2nd Product]))

lbunnell
Creator
Creator
Author

Perhaps my original expression is incorrect, but that gives me a count of 0. I'll work on it some more and let you know.

Here's an example of what the table looks like

[Customer ID][Product Type][Open Date][Account Number]AggrCount[1st Product]1stProductKey[1st Product Date][2nd Product]2ndProductKey[2nd Product Date][3rd Product]3rdProductKey
1Apples5/6/20181234563Apples1234565/6/2018Oranges1234575/7/2018Bananas123458
1Oranges5/7/20181234573Apples1234565/6/2018Oranges1234575/7/2018Bananas123458
1Bananas5/10/20181234583Apples1234565/6/2018Oranges1234575/7/2018Bananas123458
2Apples4/12/20182345672Apples2345674/12/2018Bananas2345684/30/2018
2Bananas4/30/20182345682Apples2345674/12/2018Bananas2345684/30/2018
3Oranges5/2/20183456783Oranges3456785/2/2018Apples3456796/15/2018Oranges345680
3Apples6/15/20183456793Oranges3456785/2/2018Apples3456796/15/2018Oranges345680
3Oranges6/16/20183456803Oranges3456785/2/2018Apples3456796/15/2018Oranges345680
4Apples7/2/20184567892Apples4567897/2/2018Oranges4567907/3/2018
4Oranges7/3/20184567902Apples4567897/2/2018Oranges4567907/3/2018
...

So for the expression above, I would expect a count of 2 customers for the highest ranking 2nd Product, Oranges

vishsaggi
Champion III
Champion III

Where is P([2nd Product Key]) in set analysis for Account number?

lbunnell
Creator
Creator
Author

Sorry about that. I've updated the table above.

vishsaggi
Champion III
Champion III

Can you share the app you are working on?

lbunnell
Creator
Creator
Author

Unfortunately, no. The QVW is rather large and has data that cannot be shared.

vishsaggi
Champion III
Champion III

Ok i cannot replicate coz your sample data does not have 1st prod date and 2nd prod dates can you atleast send some sample data with all the respective fields to be used in the expression.