Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Rank based on multiple conditions

Hi all

Sample Data:

CarCustomerlatest_pricelatest_timeResult needed
A1001                  102,0002019-10-18 13:03:05.0000001
A1002                    82,0002019-10-18 14:32:19.0000002
A10010                    81,0002019-10-18 14:05:17.0000004
A1005                    81,0002019-10-18 13:47:44.0000003
A1008                    70,0002019-10-18 14:56:32.0000006
A1004                    70,0002019-10-18 14:56:31.0000005
A1003                    67,0002019-10-18 13:04:23.0000007

 

Result column is the rank that I required.

Conditions is as below:

First, find latest price per customer per car. So, the data before this, will have multiple price for 1 customer 1 car. I use firstsortedvalue function to get the latest price of customer by car.

Then, what I need help with is here, I need to rank based on the data in the table above, we will take the highest price to rank but if price is the same for two customers (such as customer 1102 and 10010), we will then check the time first customer that make the booking. I'm stuck with this part now sure how to do it. This required to be done in QlikSense script.

 

Thanks

 

1 Solution

Accepted Solutions
Highlighted

I think the sorting should be ascending for latest_time as the person you booked first is ranked higher.

Order by latest_price desc, latest_time;

Also, if the rank doesn't restart, this can work too

Table:
Load RowNo() as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by latest_price desc, latest_time;

if it needs to restart for each car, then may be this

Table:
Load If(Car = Previous(Car), RangeSum(Peek('Result'), 1), 1) as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by Car, latest_price desc, latest_time;

View solution in original post

3 Replies
Highlighted
Partner
Partner

Hi Valerie,

Try this:

Table:
Load
  *,
  IF(RowNo() = 1,
      1,
      Peek(Result) + 1) as Result
Load
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by latest_price desc, latest_time desc;

It will first sort based on the price, then the time.

Jordy

Climber

Work smarter, not harder
Highlighted

I think the sorting should be ascending for latest_time as the person you booked first is ranked higher.

Order by latest_price desc, latest_time;

Also, if the rank doesn't restart, this can work too

Table:
Load RowNo() as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by latest_price desc, latest_time;

if it needs to restart for each car, then may be this

Table:
Load If(Car = Previous(Car), RangeSum(Peek('Result'), 1), 1) as Result,
  Car,	
  Customer,	
  latest_price,	
  latest_time,	
Resident [YourSource]
Order by Car, latest_price desc, latest_time;

View solution in original post

Highlighted
Contributor III
Contributor III

Thanks this works. 

Appreciate all your help and respond.