topic Displaying sum of top 10 values and Filtering based on Threshhold in QlikView

Displaying sum of top 10 values and Filtering based on Threshhold

Tue, 31 Dec 2013 10:00:25 GMT

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

Re: Displaying sum of top 10 values and Filtering based on Threshhold

mdmukramali — Tue, 31 Dec 2013 10:25:07 GMT

Dear YOusuf,

Kindly find the attachment.

I hope it will help you.

Thanks,

Mukram

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Tue, 31 Dec 2013 10:39:08 GMT

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.

Re: Displaying sum of top 10 values and Filtering based on Threshhold

sujeetsingh — Tue, 31 Dec 2013 10:52:01 GMT

Here it is

See the attachment

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Tue, 31 Dec 2013 10:58:07 GMT

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?

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Tue, 31 Dec 2013 11:39:13 GMT

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.

Re: Displaying sum of top 10 values and Filtering based on Threshhold

MK_QSL — Tue, 31 Dec 2013 11:55:27 GMT

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.

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Anonymous — Tue, 31 Dec 2013 12:01:21 GMT

1. yes use Input box.

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

anant

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Tue, 31 Dec 2013 12:10:11 GMT

Thanks Manish..

Re: Displaying sum of top 10 values and Filtering based on Threshhold

Thanmaya — Thu, 18 Aug 2022 03:49:12 GMT

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