Retrieve lowest 5 records per aggregation

[SchalkF]

Hi Qlik Guru's

I need to calculate the average time for each Product Component, and then the average of the 5 lowest TestID's run times for that Product Component.

It works fine for the first average.

I am trying to do the second average using the Rank() Function, but it is not doing the rank on TestID, but rather doing rank on Component.  This causes that the query does not use the lowest 5 run times on TestID, but to only calculate the average for the lowest Product components

The fields I have are a follows:

ProductName, ProductComponent, TestID, TestStep, StepDuration

The table uses ProductName and ProductComponent as Dimensions

My queries are as follows:

Average:

Interval((Aggr( Sum(StepDuration), ProductName, ProductComponent)/Count(distinct TestID)), 'hh:mm:ss')

Average for 5 Lowest Run Times: 

Interval(Aggr(if(Rank( (-Sum(StepDuration))/(Count(Distinct TestID)))<=5, (Sum(StepDuration))/(Count( Distinct TestID))), ProductName, ProductComponent))

Maybe I should not be using Rank() function.

Will you please let me know what you would suggest?

[Digvijay_Singh]

Your 2nd expression has Product Name and ProductComponent dimensions in your Aggr function so its aggregating your data at Component level. As I understand you are counting TestIDs in your expression so you won't get 5 different values at TestID level as you expected. May be  you would need to go one level further down and count steps instead of IDs to meet your need as u mentioned here - "but it is not doing the rank on TestID, but rather doing rank on Component."

[SchalkF]

Hi Digvijay,

Thank you for your reply.

It is still not working.  I think I might have butchered the second query, and need to go back to the drawing board, starting the query from scratch again.

If you have any suggestion on what to start off with, I would appreciate it.

Kind regards

[Digvijay_Singh]

I would suggest don't try the final expression directly, make a straight table, add all the dimensions you want to use and create individual expressions as separate measure and then add all the pieces together in the last final measure column, make a selection so that you have 1 or 2 products/components, that helps to manually verify if the expression is working as intended.

If you can share a sample, I am sure someone in the community will be able to figure out the right combination.

[SchalkF]

Test Data as below screenshot:

The duration for the above screenshot is the total time the step took to complete, to get the full duration of the test it, do a sum  group by TestID.

Required as below:

As mentioned earlier, I need to calculate the avg duration of the 5 lowest run times (TestIDs) for each ProductComponent

Kind regards

[Digvijay_Singh]

Pl share data in excel, would help to save time in manually creating data out of it in an app. Also can you share expected output at least for one case of sample data to verify the expression?

[SchalkF]

Hi Digvijay,

Please see attached excel with data.

What I would expect with my query is to see the below screenshot, but instead of a Null Value in the last column, I should see 00:00:45, since the 5 lowest TestIDs' full test duration are 37, 40, 44, 50, and 54 seconds.

Kind regards