report to only return the top 5 users

System

This discussion was created from comments split from: Return top 5 entries in a report.

sweta22

Hi @Paul Newcome,

I have a sheet that is being regenerated every 5 mins. I am trying to set a report to only return the top 5 users that is highly downgraded. I have grouped and set count. But when I am sorting it by descending order, it is not working. Can you suggest any workaround for this?

Paul Newcome

@sweta22 You would need to create a metrics sheet instead. There are a number of posts already out there in the Community detailing how to pull a "Top 5" or "Top 10" that should be able to help you get started.

Genevieve P.

Hey @sweta22

I agree with @Paul Newcome! Currently Reports can't filter based on the calculations they're created in the Grouping/Summary rows.

What I would personally do in this situation is actually add a couple helper columns in your main source, intake sheet.

Do you have a Created Date System column in your sheet? If so, we can leverage this to return a COUNT per person, but only show that count on one row per person:

Here's the formula I used in my "Count" column:

=IF(Created@row = MAX(COLLECT(Created:Created, [Shared By]:[Shared By], [Shared By]@row)), COUNTIF([Shared By]:[Shared By], [Shared By]@row), "")

Then I have a "Flag top 5" column to create a Star (or you could use a checkbox or a flag) to only flag the numbers that are in the top 5 in the sheet.

=IF(OR(Count@row = LARGE(Count:Count, 1), Count@row = LARGE(Count:Count, 1), Count@row = LARGE(Count:Count, 2), Count@row = LARGE(Count:Count, 3), Count@row = LARGE(Count:Count, 4), Count@row = LARGE(Count:Count, 5)), 1, 0)

Now you can use this "Flag top 5" column as a Filter in your Report!

Let me know if that makes sense.

Cheers,

Genevieve

Kariv

@Genevieve P. I am attempting to use your count formula for a similar scenario, but it is showing the count on every row. Do you have any other suggestions?

It would be so useful if we could sort a report based on a grouping/summary!

Thank you!

Kari

Genevieve P.

Hi @Kariv

I used a "Created" column to only show values for the MAX date (most recently created row). Is it possible that all of your rows were created at the same time? This would then show the count on all rows.

You could instead have an Auto Number column that adds a new number to each row as it's created. Then use that number column for the MAX(COLLECT part of the formula instead:

=IF(Number@row = MAX(COLLECT(Number:Number, [Shared By]:[Shared By], [Shared By]@row)), COUNTIF([Shared By]:[Shared By], [Shared By]@row), "")

Cheers,

Genevieve