Average of top n items/dimensions in a dataset

[#1]

Hi Guys,

I am currently analysing month wise average of ticket closing time. However, I want to look at just the average closing time of top 100 closed tickets/first quartile every month.

For example:

ID           Open Time    Closing Time     Month

1                  10                  15                   Jan

2                  20                   30                  Jan

3                  25                   30                  Jan

Here, if I am interested in first 2 ids of Jan, average should be 7.5 (Closing time-open time).

Can this be done on front end? I tried through set analysis but it didn't work.

Thanks.

[Anonymous]

Hi,

I think the expresion =sum(if(Aggr(rank(sum(ID)),Month,ID)>=2,[Closing Time]-[Open Time]))/2 is going to work for you.

You just have to replace 2 with 100.

Best regards,

Cosmina

[teiswamsler]

Hi Ankit

This will return the avg Closing time the hole dataset

Avg( Aggr( Sum( [Closing Time] - [Open Time]), ID, Month) )

Regarding the top 100 i whould limit the dataset in script or flag the first 100 record and add the flag i an set analysis.

ex.

Avg( Aggr( Sum( {< Top100 = {1} >} [Closing Time] - [Open Time]), ID, Month) )

/Teis

[#4]

Hi Popescu,

2 is used just for an example. Therefore, [Closing Time]-[Open Time]))/2 should be replaced by avg().
This is what I did:

1. Created a measure for average closing time
2. Added a filter using rank function:

IF

(AGGR(RANK( -ID]),4) ,[Month],[ID] ) <= 2,1,0 )

However, I am trying to make this 2 (top 2) dynamic, ie, top 50% of ids every month. Do let me know in case you have a lead.

Thanks.

[#5]

Thanks Teis, I also took the same approach by creating a flag of top 100 and a measure.

IF

(AGGR(RANK( -ID]),4) ,[Month],[ID] ) <= 100,1,0 )

However, is it possible to create a flag for first 50% rather than first 100??

Thanks

[Anonymous]

Hi,

You can create an input field Variable1, and will result dinamic, then in the expression:

=avg(if(Aggr(rank(sum(ID)),Month,ID)>=($(Variable1)),[Closing Time]-[Open Time]))

If i understood you right. Unfortunately, I don't know what other dynamic way to implement.

Best regards,

Cosmina