Top3 revenue sum to show in text object

[satishqlik]

Hi,

I have a scenario pls help

Department, revenue

A, 100

B,  600

C,  300

D,  400

E, 500

F,  400

G, 600

I want to show in object top3 sum 600+500+400=1500

Any help??

[satishqlik]

Please suggest as am strucked....

[devarasu07]

Hi,

use below expression,

=SUM(Aggr(IF(Aggr(Rank(SUM(revenue),4),Department)<=3,SUM(revenue)),Department))

Thanks,Deva

[satishqlik]

I want to show in dynamically

[avinashelite]

devarasu07‌ method is dynamic... it will show the Top values based on the Department

[devarasu07]

Hi,

Create variable vTopN and use in below expression

=SUM(Aggr(IF(Aggr(Rank(SUM(revenue),4),Department)<=$(vTopN),SUM(revenue)),Department))

Hope This helps

Regards,

Deva

[satishqlik]

As you suggested I have used your expression but not getting what exactly am looking for...Can you check it??

[avinashelite]

share the sample app and the desired output you want ...

[satishqlik]

Avinash,

Department, amount

A, 100

B, 200

C, 600

D, 400

E, 500

F, 600

G, 1100

H, 800

I, 900

J, 1000

I, 1100

L, 900

It clearly indicates top3 1100, 1000, 900 sum is 3000 this is I want to show in text object.

[ahaahaaha]

Hi,

I do not like such cumbersome expressions, but can so

=RangeSum(Num(SubField(Concat(DISTINCT revenue, ',', -revenue), ',', 1)),

Num(SubField(Concat(DISTINCT revenue, ',', -revenue), ',', 2)),

Num(SubField(Concat(DISTINCT revenue, ',', -revenue), ',', 3)))

Result

Perhaps colleagues can help optimize this expression, if this is what you need. At least it works.

Regards,

Andrey