Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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
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