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: 
dmohanty
Partner - Specialist
Partner - Specialist

Top 10 and Worst 10

Hi All,

I have a table like this below. I need to calculate Actual - Target. If the result is Positive (>0), then I need to show Top 10 CustomerID.

Also, if the result is Negative (<0), then I need to show Bottom 10 CustomerID.

I was able to create an expression till this (using a Single Field). How to show RANK using difference (Actual - Target)?

sum({<CustomerID = {"=Rank(sum(ActualSales))<=10"}>}ActualSales)

Source Table is like this:

CustomerIDActualSalesTargetSales
1100120
280100
3130150
4140110
55070
69080
7110130
8160140
9180190
10120110
11190200
12170150
13160120
14150180
1580110
167090
176050
18170160
19120100
2090100

Regards!!!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this:

=sum(

{<CustomerID = {"=Rank(sum(ActualSales-TargetSales))<=10 or Rank(-sum(ActualSales-TargetSales))<=10"}>} ActualSales-TargetSales)

View solution in original post

4 Replies
mambi
Creator III
Creator III

you can try by adding the minus sign in front of the sum function like this :

sum({<CustomerID = {"=Rank(-sum(ActualSales))<=10"}>}ActualSales)

swuehl
MVP
MVP

Maybe like this:

=sum(

{<CustomerID = {"=Rank(sum(ActualSales-TargetSales))<=10 or Rank(-sum(ActualSales-TargetSales))<=10"}>} ActualSales-TargetSales)

maxgro
MVP
MVP

=sum({$

<CustomerID={"=Rank(sum(ActualSales-TargetSales))<=10"}>

+

<CustomerID={"=Rank(-sum(ActualSales-TargetSales))<=10"}>}

ActualSales-TargetSales )

dmohanty
Partner - Specialist
Partner - Specialist
Author

Thank you very much Swuehl, and thank you others for helping.

This helped correctly. Marking it correct and closing the thread.