Multi-criteria filtering; if a filter is blank exclude that filter

[harlock]

Hello! I’m trying to setup a multi-filtering controls that filter a summary table data.

If one of the filter criteria field is empty a need the sum of all values (not apply that filter)

I could make it work with 2 criteria: date range and customer and want for example add a supplier filter.

But the formula code is becoming messy and looks not scalable.

I paste the 2 filters working formula here and try to attach a document (this is my first post):

If(
  Filters.CustomerValue.Count()=0, 
  PurchaseData.Filter(Date.Matches(PurchaseDateRange)).Cost.Sum(),
  PurchaseData.Filter(
    Customer=Filters.CustomerValue AND 
    Date.Matches(PurchaseDateRange)
  ).Cost.Sum() 
)

I can’t find a way to keep adding filters criteria to this formula.

Many thanks for your support!

[Johg_Ananda]

Hey @harlock welcome to the community!

Its hard for me to understand the exact problem you’re having without seeing an example doc, but I think the solution can be found in using other formulas. Take a look at both

switch() - https://coda.io/formulas#Switch
switchif() - https://coda.io/formulas#SwitchIf

Use good indentation and nesting in your formulas (as you have) and don’t be afraid if they grow. I have some formulas over 100 lines that switch() depending on the condition.

If you have more questions create a simplified example and the community can help!

[harlock]

Thank you for your quick answer, here it is a link for a simplified example (can’t find a way to embed it here):

example

[Johg_Ananda]

I don’t see any controls in the document, and I also cannot edit or make any changes.

@"harlock

(can’t find a way to embed it here):

To do this ‘click’ share and then ‘embed’ then ‘share embed’:

[harlock]

[Johg_Ananda]

OK based on the example you provided, can you advise what you want to happen? Give details on the controls and what the final view is.

[harlock]

sure:

in the Statistics section you see the Filters table that should be used to set the filters for the Filtered Purchases table.

Only the CustomerValue is working right now:

• if I choose a customer the Filtered Purchases table will show the sum of Cost and the sum of Price fields for that customer
• if I leave it blank the Filtered Purchases table will show the sum of Cost and the sum of Price field for all customers

The formula I need help with is inside the Filtered Purchases Table on the Cost column; that formula filter on the base of CustomerValue and Date and as I said if CustomerValue in not set that filter is ignored.

How I can add the SupplierValue (and maybe many more filter) in the Filters table to the formula so that I could for example:

see the cost and price for last month for customer greenOffice for items bought from Amazon.
or
see the cost and price for all customers for the items bought from apple
or
see the cost and price for last month for customer greenOffice for items bought form any suppliers

p.s.
Cost and Price on Filtered Purchases table are almost the same

hope this make sense for you

[Johg_Ananda]

OK the example is very helpful here. Try this. Instead of creating a new table for Filtered Purchases, create a VIEW of Purchase Data.

Then add two new controls to the document, one as a Single Select and one for a Date Range. Set the single select, and point ‘Selectable Items’ to be a formula pointing to ‘Customers’. Name this ‘Customer Control’. Then add a date range control. It’s default name will be ‘daterangepicker 1’.

Now set your new ‘view’ table to be filtered based off these controls. You can see my formula is:

thisRow.Customer.Matches([Customer Control]) and thisRow.Date.Matches([daterangepicker 1])

I think this should be enough to get you on your way. Good luck!

[harlock]

…your suggestion make sense but I choosed to use a separated table instead of a view because my Filtered Purchase table is always one line and show always the sum of Cost and the sum of Price for the filtered items.
With your approach if I have hundreds of purchases the view table will show, even with filters, many rows and I have to scroll down to see the totals.

What I’m try to achieve is to have one row with totals based on filters I choose…

[Johg_Ananda]

OK I think I’m tracking you. Can you make my suggested changes to the doc you’ve shared and then I’ll hop back in and try to create your single line view you are looking for?

Can you also scaffold out an example of what you want the final view to be (specify the control states) and what you expect the output to be.

[harlock]

Hello, I scaffold out an example with your suggestions.

In the Statistics section you see a data range picker, a customer selector, a supplier selector and an operator selector.

What I want to achieve is filter the Purchase Summry table with those selectors so that it show me the filtered rows.

For example I want to see all the last week purchases for redOffice, bought from amazon and assigned to operator Brad.
If one filter is blank (for example no operator) that filter should be ignored (show all operators)

Lastly I would like that the filtered results rows will be summarized (reduced) to one row with total cost and price for that selection as I did in previoud example when I used a dedicated new summary table instead of a view.

many thanks again

[Johg_Ananda]

OK we’re getting closer. Those controls are great. You can link them to the table by opening up the table filter and creating formulas that reference them.

A more accessible way is to use the ‘interactive filter’ option for each column you want to filter by. Right click on the column and choose interactive filter. It looks like it will even try to match up to your existing filters. Here’s how you do it:

[harlock]

…I see, that’s another way to filter columns…interesting but it doesn’t resolve my problem: I need the option to leave the filter blank to see all results.

I ended up using “multiple select controls” that have the select all options. I don’t like the design and usability of this controls I woult prefer the “single select ones” but seems that currently is the only way to have the “all” option.

thank you very much for your support

this is my solution: