Displaying tkt # associated to Top 3 values

[dacards]

I have a collection of data on trouble ticket information. Trying to calculate Mean Time To Repair. I’m displaying the total average and then “attempting” to display the 3 tickets with the highest MTTR and the ticket #s that are associated with those MTTR values (which is where my problems is at).

This calculates MTTR for each ticket:
@MTTR = ({MOD_TRB_TroubleAuditTrailJoin.Ticket Resolved Time}-{MOD_TRB_TroubleAuditTrailJoin.Event Start Time})*24

The below 3 Running tables calculates the 3 highest MTTR values, and are set up as follows:
#MTTR1 =
@MTTR maximum EVALUATE “on change of field” MOD_TRB_TroubleAuditJoin.TTRequest ID

#MTTR2 =
@MTTR Nth Largest (2) EVALUATE “on change of field” MOD_TRB_TroubleAuditJoin.TTRequest ID

#MTTR3=
@MTTR Nth Largest (3) EVALUATE “on change of field” MOD_TRB_TroubleAuditJoin.TTRequest ID

This goes through my data trying to find the ticket that matches to the above 3 highest values. I have this duplicated for #MTTR2, and #MTTR3:
@MTTR1=
whileprintingrecords;
stringVar mttr1;
if {@MTTR}
= {#MTTR 1} then
mttr1:= {MOD_TRB_TroubleAuditTrailJoin.TT Request ID};
mttr1

This formula displays the ticket #s that match the highest MTTR values:
@Display MTTR1=
whileprintingrecords;
stringVar mttr1;


Using my running totals, I can find the 3 highest MTTR values every time, problems arise though when displaying the ticket #s that are associated with these 3 values. When looking at the data it grabs the first MTTR and assigns it to MTTR1 (as this is the highest MTTR since it’s only looked at one recored). It then goes through the rest of the data and if it finds a tkt with a larger MTTR it assigns it to = MTTR1. The problem appears to be, if it finds the 2nd Highest MTTR before it finds the largest, it does not go back up to reassign the 2nd Highest. When this happens my results will show the values for the Top 3 MTTRs, but you only see the ticket # associated with MTTR1 and MTTR3. (this can happen with MTTR3 as well with the same circumstances).

 

[lbass]

Perhaps this could be resolved by sorting on {@MTTR} in descending order?

-LB

 

[dacards]

I have tried that and it would work, but let me add more to my story. I'm doing similar formulas for the following metrics:

MTTR
Avg Time Before It Is Assigned
Avg Time To Resolve When Tkt Is Sent Back.

So I've got all three, where I'm reporting the AVG, and then the TOP 3 values (with Tkts #s) for each. So if I were to sort by MTTR, the others are out of whack. Hopefully this helps, and as always thank you for replying.

 

[lbass]

I don't think you should use running totals. Instead create a formula like:

whileprintingrecords;
stringvar idmax;
stringvar id2nd;
stringvar id3rd;

if {@MTTR} = maximum({@MTTR}) then
idmax:= {MOD_TRB_TroubleAuditTrailJoin.TT Request ID};
if {@MTTR} = nthlargeset(2,{@MTTR}) then
id2nd:= {MOD_TRB_TroubleAuditTrailJoin.TT Request ID};
if {@MTTR} = nthlargeset(3,{@MTTR}) then
id3rd:= {MOD_TRB_TroubleAuditTrailJoin.TT Request ID};

Then create three separate display formulas like this to display in the report footer:

whileprintingrecords;
stringvar idmax;

If you are doing this at the group level, you'd need a reset formula for the variables to be placed in the group header, and you'd have to add a group condition to the maximum and nthlargest arguments.

-LB