Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
Maybe like this:
=sum(
{<CustomerID = {"=Rank(sum(ActualSales-TargetSales))<=10 or Rank(-sum(ActualSales-TargetSales))<=10"}>} ActualSales-TargetSales)
you can try by adding the minus sign in front of the sum function like this :
sum({<CustomerID = {"=Rank(-sum(ActualSales))<=10"}>}ActualSales)
Maybe like this:
=sum(
{<CustomerID = {"=Rank(sum(ActualSales-TargetSales))<=10 or Rank(-sum(ActualSales-TargetSales))<=10"}>} ActualSales-TargetSales)
=sum({$
<CustomerID={"=Rank(sum(ActualSales-TargetSales))<=10"}>
+
<CustomerID={"=Rank(-sum(ActualSales-TargetSales))<=10"}>}
ActualSales-TargetSales )
Thank you very much Swuehl, and thank you others for helping.
This helped correctly. Marking it correct and closing the thread.