Only show the largest entry in a group

User Talk
1

Hello,
I have a table of product purchases that I have grouped by item number and sorted by purchase date. How can I get it to display only the last purchase of each item number (and not all of them)?
Thanks for your help.

2

You can use a link formula column and use the findmax formula.

image
image420×543 30.1 KB

3

I’m completely new to seatable and don’t understand how to use the findmax formula inside one single table. As far as I understood it needs two tables to work.
My table has 2000 rows with the different purchases of about 200 items (SKU). If I now group by SKU, all purchases of the respective SKU are listed one below the other. Now I can sort the display by delivery date in ascending or descending order.

However, I only want to see the last purchase with the most recent purchase date.

4

Link formula columns operate on a link column which (usually) require two tables.

SeaTable does not offer a filter “largest/smallest value”. But there is a way to achieve the desired result:

  • Add one column of type number (I’ll call this column “Rank”)
  • Run the data processing operation “Calculate rank” on the date column using the ranking method “Larger value rank first”, the result goes in the column “Rank”
    image
    image383×507 24.2 KB
  • Create a view filtering on the column “Rank”; the filter should be “Rank = 1”
    image
    image411×134 3.78 KB

All features referred to above are included in the Free plan. The drawback: You have to rerun the data processing operation manually.

With the Enterprise plan, you can create a automation rule that calculates the rank periodically without your input.

5

Thanks for the suggested solution.

However, I have first grouped by item number and only want to see the last purchase of each item.

After grouping, I then have 200 items with 5-20 purchases each.

The Calculate Rank function creates a ranking of 1-2000 across all items in the newly inserted “Rank” column, and not a ranking within the grouping (per item).

Or is there any way to modify the function?

Thanks again for your help.

6

Modify your data model as follows:

  • Table “Items”
  • Table “Purchases”
  • Add a link column
  • Link the purchases to the items (you can use the data processing operation “Auto add link”)
  • Add a link formula column and use the findmax function to show the date of the latest purchase

You currently use SeaTable like a spreadsheet.But SeaTable is a (relational) database. By using like a (relational) database, you can seize the full power of SeaTable.

7

Thank you for the further explanation. Now I (hopefully) understand it.

8

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.