Okay I am ready to ask others for help here: I am being asked to look at how a constituent interacted with our organization before they registered for TOT. I have an LOD formula Called Tears of Things Registration Date that give me the date of that registration. However, what I now need is to set up a set of formula's that look at the created dates or close dates of other interactions and count only if the TOT Registration date is great than the other fields. It doesn't seem to be working like I expected. Here is an example: Word Problem I am trying to solve: At the contact level, count how many Registrations are Online Education, Attendee, Registered and created date is less than TOT Registration Date. { FIXED [Contact ID]: COUNT(If [Event Type (Finance)] = 'Online Education' AND [Category] = 'Attendee' and [Status__c (Registration)] = 'Registered' Then Date([CreatedDate (Registration)]) END <[Tears of Things Registration Date]) } Can anyone help me adjust this formula to actually get that number? Right now it is registration counted not those that are before the TOT Registration date (which could be as far back as a year ago).
you have what is known as the 2-date problem - your data has a start and end date for each record - the dates are independent dimensions and you want to find out what happens between the dates - for that you need to scaffold the data - see https://jimdehner.com/2021/02/19/faq-series-when-and-how-to-scaffold-data/ which explains the issue and https://jimdehner.com/2025/01/27/say-goodbye-to-cartesian-joins/ example 2-3) which will walk you through the solution without using a Cartesian join which explodes the data
