Formulas to filter by a date range in Lucid

Okay, I'm going to show an example. Well, hold on, let me get the right tab here. Here we go. Uh, of how you can filter down dates in Lucid. Um, so I have for myself here this Google sheet that I made with just a bunch of fake names. This is a pretend recruiting example. And um, I've set this up with there's a whole bunch of  dates. Keep in mind these are US format, so month, day, year. um you might have to adjust the formulas if you're using a a different date format like um day, month, year. Uh yeah. So in my board here, I've actually linked it using data linking. Uh in fact, I just imported that using the import data and brought everything in. So this is my Google sheet and it'll stay up to date. And then what I'm doing here is I'm leveraging some formulas. Now in my case, I'm just going to do a really simple example where I'm counting up how many applicants between this date range. You could do much more complex things, but we're going to stick with a basic example. So, how did I set this up? Well, I added a text box and a little dash. And I locked both of those to the canvas so that they can't be moved. I then added two other text boxes. These are also locked, but not the content, meaning I can still come in here and update something. The really important thing here  though is that I added data uh over on the side. So I added a field called date that is actually splitting what is typed in with uh the forward slash here. So if you type the date in using dashes, this is not going to work. Uh you could update the formula to work differently. Uh but it's going to split that and therefore it's going to give me an array of 15 2024 with my years here. I have another field here called calc lower date where I'm then actually turning this into a date from text. And I chose to do this just because it's a little bit easier than having to write split every single time. So I'm saying, all right, for uh I'm gonna make a date using the  date formula. And I'm saying pass in first off the third item in that list, which is the year. Then pass in the first item, which is the month, and then pass in the day. So when we make a date in Lucid, it takes year, month, day. And therefore using the American date format I have to do 3 1 which is the month 2 which is the day in order to get that. Then I'm actually wrapping that entire thing in an if error because if for some reason like if I have this as like a key smash then I just want this to figure out the error and I want it to display a date anyway. So what's that? Um, I am just putting in as a date. If it totally errors out, then I want you to use January 1st of this year. Again, you could update that. I'm doing the exact same thing on this one, but very importantly, I named it something different. Calc upper date. Um, and that is important because I have to find those unique input fields later on. Then, in this text box where I'm actually displaying it, uh, I've done a couple things here. First off, I went ahead and made two properties, lower date and upper date, where I'm literally just going and pulling what was input on the page. So, I'm searching for all the shapes on the page, specifically for the property called calc lower date. Well, guess what? There's only one of those. I made sure of that. I made it very uniquely named. Uh, so I'm only pulling the calc lower date, and that's just going to give me that exact date. I'm doing the exact same thing with the upper date. So, these input fields are super unique and therefore easy to find. my text box. I just chose to give myself an easy reference to them so that I didn't have to use this whole string uh children.page calcate in my formulas here. So then I get to applicants because that's what I'm trying to figure out is how many in this case I have a column called date received. Uh so how many applications were received between this date range. Um so I am using a lookup. In fact I'm saying lookup sheet one which is the title of this data set. If I had called it something else in Google sheet, I'd be typing that here like main or applicants. Then I'm saying for every applicant, which I've just done as app, this could easily be X, it could be Y, it could be whatever you say here. After this little arrow, I say we're going to check two things. So that's this and. So I'm going to check is it true that the app.date received because lookup is going to let me get each row. And then I can just check the field date received. Is that greater than or equal to the lower date that I pulled? And is the date received less than or equal to the

upper date? Uh you can adjust this as much as you want. And then uh this was all wrapped in the filter so that I'm going to filter down to just those items. And then I wrapped it all in a count function which gives me 14. I then did the exact same thing but I checked the date interviewed column because that's another column that I've got. Um, and then I did the exact same thing for the column called date offer. Um, and all of those essentially are going to tell me. So if that offer was extended, then we do end up counting those dates because otherwise it's empty and we're also checking the date range. So all of that I then was able to just click the

plus t to insert those total counts. And we'll see that if I were to update this. Um, so this goes back as far as January 2nd. So let's actually switch this to the second. And you'll notice that that number went up. And if I say that this goes all the way to March 15th, that number goes up again. Uh and I did check this against the sheet. So this is accurate. Um but this is exactly how we would go about creating that sort of date range so that we can uh input it directly within Lucid and see those numbers change from here. You could use this in a number of other ways. I could use similar formulas to start displaying something with maybe Lucid's  dynamic shapes if I wanted to. But this is just a basic example to get started.