Displaying sum of top 10 values and Filtering based on Threshhold

[#1]

Hi Experts:

I have 3 simple requirements

1- Enter a Treshhold Value (%) : Beside this text object there should be an input box or text box (i am not sure which is the best option..hence, have not added any object)..... if i enter 10 in the textbox/input box, the sales figures should be divided by 10 in the adjacent table

2- The sum of top 10 values from the table should be shown in the text object beside 'top 10 sales'

thanks a ton in advance

[mdmukramali]

Dear YOusuf,

Kindly find the attachment.

I hope it will help you.

Thanks,

Mukram

[#3]

Hi Mohammad,

Thanks for your prompt reply.

The first ask is done. However, i believe you misunderstood the 2nd ask.

I just want the sum of sales for the top 10 sales to be displayed in text object.

[sujeetsingh · Accepted solution]

Here it is

See the attachment

[#5]

Thanks.

one final query. Suppose we dont have any variable. and we just need to display sum of top 10 values from the table and our table has 15 sales person. Then what would be the expression?

[#6]

In Other words, i just have a table and just a text object in which i have to display the sum of top 10 sales.

[MK_QSL]

Use below expression...

=SUM(Aggr(IF(Aggr(Rank(SUM(Sales)),[Sales Person Name])<=10,SUM(Sales)),[Sales Person Name]))

Instead of 10, you can create an input variable with vTOP and use $(vTOP).

=SUM(Aggr(IF(Aggr(Rank(SUM(Sales)),[Sales Person Name])<=$(vTOP),SUM(Sales)),[Sales Person Name]))

I haven't taken threshold value.. Please add yourself as your requirements.

Hope this help.

[Anonymous]

1. yes use Input box.

2. use Rank function  aggr(if(Rank(sum(Value)<=10),sum(Values),yourdimension)

anant

[#9]

Thanks Manish..

[Thanmaya]

Hi Sir,

I want to know how can we achieve the below scenario.

I have 10 records with a cycle time field. I want to calculate average of 90% of min cycletime records.

Eg :

Id    Cycletime

1      20

2      25

3      30

4       40

5      45

6      65

7       70

8      70

9       70

10     150

In general ,  average of the above records is 58.5.

Here , i want to calculate average of 90% of records with minimum cycle time, that is 150 cycletime should not be considered.

90 % of records : 435/9 = 48.3 

How can I achieve this scenario. Please help