Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
eddywong71
Creator
Creator

How to Rank Data not by month year.

My Dataset like below.

 Month Cust_ID Balance

  202101   a1       100

  202102   a1     200

   202203   a1     300

202203    a2     400

I am using AGGR(RANK(SUM{$} Balance)), Cust_ID). The expression is work fine when Month selected. 

However, when user don't select any month. The same CUST_ID sometime rank are missing.  I want the table include all month is rank by the balance.So That result like below 

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 2    202203   a1     300

 1    202203   a2     400

Labels (6)
1 Solution

Accepted Solutions
eddywong71
Creator
Creator
Author

Rank(Total Cust_ID)  solve my problem

View solution in original post

6 Replies
Onkar10
Employee
Employee

In Rank field use: AGGR(RANK(SUM(Balance)), Cust_ID,Month)

eddywong71
Creator
Creator
Author

Hi Onkar10 ,

I try this expression before. However, this is like group by ID, Month.

The result will like this. 

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 1    20203   a1     300

 1    20204   a2     400

 

Let my reclarify i want the result like below

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 2    202203   a1     300

 1    202204   a2     400

Onkar10
Employee
Employee

= AGGR(RANK(SUM(Balance)), Month)  

This should give you result you want.

eddywong71
Creator
Creator
Author

Hi Onkar10, would you try the data like 202203   a2     400. I dont know why my result rank still not accurate.

I think maybe Cust_ID appear more than one. But after month filter Cust_ID  will be distinct.

Onkar10
Employee
Employee

Same result with :202203   a2     400

eddywong71
Creator
Creator
Author

Rank(Total Cust_ID)  solve my problem