Top 10 and Worst 10

[dmohanty]

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:

CustomerID	ActualSales	TargetSales
1	100	120
2	80	100
3	130	150
4	140	110
5	50	70
6	90	80
7	110	130
8	160	140
9	180	190
10	120	110
11	190	200
12	170	150
13	160	120
14	150	180
15	80	110
16	70	90
17	60	50
18	170	160
19	120	100
20	90	100

Regards!!!

[mambi]

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

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

[swuehl · Accepted solution]

Maybe like this:

=sum(

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

[maxgro]

=sum({$

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

+

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

ActualSales-TargetSales )

[dmohanty]

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

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