Filter Values when creating a Summary Table?

Ask for Help
1

Is it possible to create a Summary table with a ‘pre filter’, so that the resulting table already has some records from the source table filtered out?

For example, suppose I have a table with 100 records, and 20 of them have Status=Ignore. I only want to create a Summary table on the other 80 records (where Status != Ignore).

I’m aware that I can create a Summary table and then filter out those records, but the downside of that method is that it shows the filter and allows it to possibly/accidentally be cleared/reset, re-introducing the records that I don’t want to be present. Granted, I can “unpin” the filter, but it’d still be there in the Table filter settings.

If this is not possible, this would be a very powerful feature! I would envision this being part of the Summary table creation process (possibly in a default-collapsed ‘Advanced’ section, to keep the current flow simple like it already is). Alternatively, add some option in the right sidebar ‘Table’ tab to set one or more persistent filters that are not exposed in the page where the summary table is rendered, which is separate from the existing Sort/Filter UI.

1 Like
2

Wow. what a coincidence - I was also thinking of posting for a feature like this. +1

3

Hey there,

That’s not currently possible but a great idea for a future improvement. I have shared it with the team!

Thanks,
Natalie

4

Hello,

in addition to the initial request, I have an extra usecase where this would be very useful: When using this filter when creating the summary table it would not appear as a column in the result, so the lines would be grouped with all the values of this field.

Otherwise, when creating the summary table with multiple group by the fields are all displayed and I cannot get a subtotal easily.

Or am I missing something and a way to do it ?

Thanks a lot,

Laurent

5

I agree, it would be helpful for Summary tables also to have these sort of filter fields.

Re: your question about Summary tables, the group-by columns you choose are what dictate any of the aggregations that you have (average, sum, min/max, etc.). In general, it doesn’t matter whether the fields are visible or not, so you can hide whatever fields you don’t need (i.e., the fields that you aren’t grouping by). If you are not getting things working, maybe you can provide an example with mock data, or share out a copy of your Grist doc.

6

Hello again,

Here is an example : Example Employee - Grist

I want to filter the “Gone” Employee, but I do not want to have the details with the others types of contracts to have the actual count of all employees that are present per team. I would like as a result of the summary table to have only two lines with sales and tech team.

I know I could have a special column only for this field but in some other cases I want to be able to filter many datas, or I could create a table from scratch , but it would be annoying to put the filter in everycolumn and build everything.

This is an example but generaly to have summary tables with a “where XXX” condition would be great when creating the summary table.

Thanks a lot,

Laurent

7

You can create a Summary table grouped by Team, then modify the default “count” column formula so that it excludes employees with Status=“Gone”:

sum(1 for r in $group if r.Status != "Gone")

See new Summary table widget here:
https://docs.getgrist.com/kGiTiQeo1qbC/Example-Employee-copy?utm_id=share-doc

T

8

Yes, the only problem is that if there are many columns I need to put that filter in each. This is why a global filter for summary tables would be interesting ! Many thanks for your suggestions !

9

Ah, got it, good point. Maybe take a look at this post?

10

Very interesting thank you, it could help. I only wish it was a basic feature of grist.

Thanks a lot for the link !

11

I implemented successfully the filter option with the isSelected formula. Nevertheless it forces the creator to have fields without selection and fields with selection (so I duplicated every column BLABLA to BLABLA selected with the extra r.isSelected condition).

So very nice approach but it needs some extra work :slight_smile:

12

@Laurent_Joubert a user replied to your comment but the email only went to support@getgrist.com. I am posting it here.

For reference/learning, can you share a copy of your document showing this?

Steps on sharing a document publicly can be found here: Sharing a document - Grist Help Center

If it has sensitive data, you could save a copy as a template and share that version instead - details here: Copying documents - Grist Help Center

Thanks!

13

Hello Natalie,

here is a template of the document (with column names mostly in French) https://grist.numerique.gouv.fr/o/docs/vHbkd1kexwQC/Budget-template?utm_id=share-doc

This is my first grist document and I have not a lot of experience but this are the comments I have coming from an Excel world:

  • I really enjoy the Page / VIew / Table difference. One suggestion, we cannot save a view. For example if I want to update the disposition of the Options table on a page, I need to do it again on every page I use it. It would be great to have the possibility to save a view and say on a page that this table is displayed under this “saved_view” view so that it can be automatically updated if I change the “saved view”
  • In the example I would like to display totals with different financers but still be able to filter some of the financer and not have details about the others. This is how I managed to do it with the SEL (for selection) pages, but still I think it would be easier if there was a customisable “where clause” on summary tables, but maybe this is the way to go.

Let me know if there are better ways to create the same kind of reporting.

Thanks a lot,

Laurent