Sum of Top N records with positive value

[rohanmayekar]

Hi,

I have created sample table to support my qn.please see below.

I need to calculate sum of top 7 & bottom 7 DIffSalary and compare against total Positive values .

i am using following expression but not getting the desired result,

Top 7:

=sum({<Name={"=rank(sum(DiffSalary))<=7"}>} DiffSalary)

Bottom 7 :

=sum({<Name={"=rank(sum(DiffSalary))>$(=Max(aggr(rank(sum(DiffSalary)),Name))-7)"}>}DiffSalary)

Note: While calculating top 7 value i need to exclude negative values.

Name	STDSalary	ActualSalary	DiffSalary
Josh	2020	3000	980
James	7000	7500	500
Ron	6500	7100	600
may	5000	5100	100
Lois	2700	2901	201
rock	990	1001	11
jaimey	990	878	-112
Lisa	900	800	-100
diana	1767	1600	-167
hamosh	2000	1800	-200
ali	1200	980	-220
wilfred	1300	1000	-300
kusaick	6500	6000	-500
kamran	1200	987	-213
daisy	2323	1200	-1123
linda	2000	200	-1800
alex	17899	15000	-2899

[sunny_talwar]

Both expression don't work or just the bottom one doesn't work?

[sunny_talwar]

For bottom, you can try this

=sum({<Name={"=rank(-sum(DiffSalary)) <=7"}>}DiffSalary)

[Anonymous]

For top 7 excluding negatives try

=if(ActualSalary-STDSalary>0,sum({<Name={"=rank(sum(DiffSalary))<=7"}>} DiffSalary))

[rohanmayekar]

Not working.

[rohanmayekar]

Hi,

The condition check before calculating Top10 and bottom 10 is as below,

1) IF actual- standard is positive => what are my top 10 salaries

2) IF actual- standard is negative=> what are my bottom 10 salaries

[Anonymous]

Adding to sunny's suggestion :

=sum({<Name={"=rank(ONLY({1}(-sum(DiffSalary))) <=7"}>}DiffSalary)

[rohanmayekar]

This does not work

[rohanmayekar]

Hi All,

Following expression giving error,

=sum({< STDSalary-ActualSalary = {">=0"} >}STDSalary-ActualSalary)