Help with Formula (doesn't contain)

1

Hi all,

I’m looking for a bit of help…

I’m trying to get a count of the number of booking that contain any of “cancelled” or “rejected” but none of “booking”. Each deal could have all three, so I’m just trying to count the number that don’t explicitly have “booking”

I’ve got a filter set on a date toggle, so I can accurately see the data in that time frame. I’ve included the source code below.

[All Deals].Filter([Vetted Date]>=todate([Curation Lead Funnel Filter].[Start Date]) && [Vetted Date]<=todate([Curation Lead Funnel Filter].[End Date]) && ([Deal Stage]=“Event Completed / Contract Exchanged” OR [Deal Stage]=“lost”) && [Status (from Bookings)!=Contains(“Booking”) && [Deal Source]=thisRow.Funnel).Count()

2

Hi Jack,

Welcome to the community!

I see a couple of issues in the formula, but without seeing it in the context of the doc I cannot be sure that my corrections will be enough to make it work, so if you could share a dummy doc it would be very helpful.

First I just rearranged your code without changing anything just so it’s easier to read.

[All Deals].Filter(
  [Vetted Date]>=todate([Curation Lead Funnel Filter].[Start Date]) 
  && [Vetted Date]<=todate([Curation Lead Funnel Filter].[End Date]) 
  && (
    [Deal Stage]=“Event Completed / Contract Exchanged” 
    OR [Deal Stage]=“lost”
  ) 
  && [Status (from Bookings)!=Contains(“Booking”) 
  && [Deal Source]=thisRow.Funnel
).Count()

A couple of things:

  • If you set up the date columns correctly, todate() is in most of the cases not necessary.
  • You made a mistake with the Contains() formula → [Status (from Bookings)].Contains(“Booking”) .not()
  • In filters with many conditions like this, I would recommend removing the conditions one by one until it works as expected, so that you are able to pinpoint the problematic one.

Not sure if this will be enough to fix your issue. If not, please share the doc.

Hope this helps,

Pablo

3 Likes