Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
SchalkF
Contributor III
Contributor III

Retrieve lowest 5 records per aggregation

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?

Labels (4)
6 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Test Data as below screenshot:

SchalkF_0-1662123902204.png

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:

SchalkF_2-1662124214968.png

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
Contributor III
Contributor III
Author

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.

SchalkF_0-1662126093218.png

 

Kind regards