Limit rows on a report

ScottDouglas

Sorry if this has been asked somewhere else.I am new to SmartSheet and have had a look in community but couldn't find an answer.

I have a Kanban board setup in SmartSheet and have a few reports running that are displayed within a dashboard

Currently I have a report that pulls all items and orders by a creation_date field (oldest to newest) I am trying to limit the report to top 10 items only so that the dashboard is a highlights rather than all data.

 

Any help much appreciated

Paul Newcome

You would need a helper column on the original sheet. We can use a Checkbox type and call it "Helper".

 

The formula that goes in this column will depend on what you mean by "Top 10 Items". Is it the 10 most recent, the 10 oldest, or is it based off of another criteria?

Nate H

@Paul Newcome It doesn't appear that Scott replied, but I'm trying to do something similar to Scott. In my case I'm wanting to limit to the most recent 4 rows of data which would be based on the "Ending DOS" date column. I'm trying to limit this for the purpose of a line chart on a dashboard. In order to get the line chart to work as needed, I had to create a pivot sheet and built a report from that. In doing so, however, it converted the date column format so I can't do the standard filter for the last x days.

Any suggestions?

Paul Newcome

@Nate H You should be able to use a formula on the source sheet to flag the rows with the 4 most recent dates and then pull a report from there or are you meaning something different? Are you able to provide screenshots for reference/context?

Nate H

@Paul Newcome That is what I'm looking for. Keep in mind that this is a pivot sheet that changed the format of my date column.

Paul Newcome

@Nate H Are you able to insert a new date type column into the pivot sheet and have it stay as is when the pivot app updates it?

Nate H

@Paul Newcome I really don't know. This is my first time using Pivot. I'm scared I will mess it up. :) Let me build out a test kitchen and try it. What are you thinking?

Paul Newcome

I was thinking we could use a formula to convert the text string into a usable date, but really (if you can insert a column and use a formula without it being affected by Pivot updates) we can just convert it into a number and then add a second column to flag if the row is one of the 4 largest numbers (easier than trying to convert it into a usable date).

First column:

=IFERROR(VALUE(SUBSTITUTE(Rows@row, "-", "")), 0)

Second column (filter on in report):

=IF([First Column]@row>= LARGE([First Column]:[First Column], 4), 1)

Nate H

Well, I've said it before and I'll say it again - @Paul Newcome , you're a genius. Worked like a charm. Thanks!

Paul Newcome

Happy to help. 👍️