Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for 
Search instead for 
Did you mean: 
deepanshuSh
Creator III
Creator III

Nested Ranking using multiple Dimensions

Hi Fellow Qlikers, 

I need help with getting ranks for my data, the issue is that when a dimension value is having a same rank i.e 4-5, I need to sort that value based on another field, I have tried doing it but it seems to be not working as I am getting 1 as the same rank for all the values. 

Basically the data currently looks like this. 

Expression in Rank 1: 

Aggr

(    Rank (sum({<Recommendation_type={'Single'}>}[Recommendation Confidence]))     ,[Product Recommendation])
 
Rank 2:
        Aggr
(
            Rank 
(
    sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])
                )     ,[Product Recommendation], [Recommendation Lift])
       
 Rank 3: 
Aggr(
Rank (
        Aggr
(
            Rank 
(
    sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])
                )     ,[Product Recommendation])
       
         ) , [Recommendation Lift]) 
       
         

deepanshuSh_1-1685622301658.png

 

 

I want to sort the rank 4-5 into 4 & 5 giving [# 175 - Polymer Wax Polish 250 ml] as 4th rank and [# 170 - Wax Shampoo 250 ml] as 5 th rank. 

Meaning that I want them to be sorted on lift if the ranking is coming out to be same and want to show it in the Simple KPI rather than the chart. 

Do let me know if anything further can be provided for understanding the issue. 

 

 

 

Trial and error is the key to get unexpected results.
Labels (6)
1 Solution

Accepted Solutions
Kushal_Chawda

@deepanshuSh  try below

=aggr(rank(sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])*1e10 + [Recommendation Lift]),[Product Recommendation])

or

=aggr(rank(sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])*1e10 + sum(distinct {<Recommendation_type={'Single'}>}[Recommendation Lift])),[Product Recommendation])

View solution in original post

6 Replies
Kushal_Chawda

@deepanshuSh  Is recommendation lift dimension or measure?  How recommendation lift value derived for Product Recommendation? Is it sum(Recommendation Lift)?

deepanshuSh
Creator III
Creator III
Author

Thanku for the response,

Recommendation lift is a dimension. Each product recommendation is linked to a concatenated product name and each product recommendation has their own specific lift value.

Trial and error is the key to get unexpected results.
Kushal_Chawda

@deepanshuSh  try below

=aggr(rank(sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])*1e10 + [Recommendation Lift]),[Product Recommendation])

or

=aggr(rank(sum({<Recommendation_type={'Single'}>}[Recommendation Confidence])*1e10 + sum(distinct {<Recommendation_type={'Single'}>}[Recommendation Lift])),[Product Recommendation])
deepanshuSh
Creator III
Creator III
Author

Hi  @Kushal_Chawda , thanku for the response. 

What does this 1e10 Represent here?

Trial and error is the key to get unexpected results.
Kushal_Chawda

@deepanshuSh  It's representation of Number 10000000000. 

deepanshuSh
Creator III
Creator III
Author

Hi, Thanku Kushal for the help. This partly solves my problem along with another workaround. 

Apologies, I could not respond earlier, due to some issues with my laptop, Thanks for the help. 

Trial and error is the key to get unexpected results.