Hello. Today's question comes from Mohammed. He actually asked this on Doc Williams' channel. Thanks Doc Williams for making some more videos from our talk available. If you are not subscribed to Doc Williams. Now. Go over. To Doc William's YouTube channel and take a look at what he's doing.

Moham. So he has a database and he has a table of data and he wants to create a bar above that data, having following buttons. And from what he's saying, it sounds like he has a database of cars. So he wants to have a start date, an end date, a car type like Nissan or Toyota or Nissan and Toyota, and a country.

So once he's chosen these filters, he wants to then reflect in the raw data. And I believe what this means is that he wants to see only the results that he selects or wants. And so I think I'm gonna make a few assumptions totally understandable if these assumptions are wrong in any way or the angle is off.

But basically, I think this is the answer. , sorry. The actual problem is he has a list of cars. He wants to filter out everything that is outside of the start date and end date. So I'm not sure if these are particularly like date of sale or date of creation. There is a little bit of, sort of interesting things you can do with dates.

So I think this will be an interesting video to show you how to filter between two dates. I'll show you that. And then. What could be an entirely other video but I'm gonna try to get through it in this video as quickly as I can, is car type, which he has two particular problems here. So he's asking for a car type Nissan only, or Nissan and Toyota.

And in order to solve this challenge, in this type of filter where you can have sort of an or, or an and Or sorry, one option or more than one option? There's a few ways to deal with this. I'm gonna show you one way. It's not the only way. And then just a country, which where I guess you selected.

I think this is going to be I actually can't tell because it, the, the country he has usa but the car types are Nissan and Toyota. So maybe this is car sold. Unfortunately, sorry. It, it's just a little complicated question because the answers are vastly different. If there is some issue here, so I'm gonna get through this as best I can with the information, I can Absolutely understandable if this is not the correct answer.

But the very first thing I'm gonna do is I'm gonna create a list of cars with this kinds of data, these kinds of data. And I'll be back in like one second. All right. So the first thing we have is a. And I'm gonna show you how to set this up because we need to, in order to accomplish what he's asking, he's asking for a database function where you have a, the table of raw data.

So you see everything, I'm assuming, and then you have a bar above that that you can filter. So first you actually have to have a database on a separate tab so that you can filter it. So here I've created it. I've created IDs so we know which what we're getting. We're getting some other information other than just.

the type and the country. I've added Mexico here, some other country. These are totally random. I also don't know the exact other I don't know if this is a data sale or date of purchase or anything else, but we have some random dates here. And what we're gonna do first is go over to our filter and we're gonna create everything.

So we're gonna go filter. What do we wanna filter? You wanna filter?  A to D, and actually we wanna do a two, two D. And we're just gonna do 16 just to, we know the end of it. This might be everything, but we're just gonna get that filter. We're gonna get everything. So we need to filter something. So actually let's start saying what we want.

We want everything with id , we want everything that has an id. So that's over zero and that's going to need to be the same thing. Basically it needs to look at the exact same range, so your database and your filter need to be the same thing there, or you'll get an error. Okay? So we have everything we have.

Let's me, let me put ID here. Date type.  in country. So let me do one more thing here and we're just gonna make this a little nicer to look at. So we know that this is our database. I'm gonna put a line here actually. We'll see. You'll see why in just a hot second. We're going to hide our good lines.

Let's make it look a little nicer. And now we want to filter so we. As he says, start date, end date. And we're gonna be searching between those two dates. I'm going to just add a couple dates here. Once you add a date, you can double click and you can sort of just say, here, let's do April 1st to there.

What is the other thing we want? Type, so we wanna select the. And we wanna select the country. Okay? So if we want to make a dropdown menu, you'll see this list from a range. We're going to go back to our database and we're gonna select country. Okay. Save, we can definitely fix this. And we have a couple extra things we can do to this, but check out the other Better sheets, videos.

Those are available to make this a little more nuanced and easier. Work with this type. So this is the hardest part of this entire video, is how do we select multiple types of cars. So let's go through the filter of start date. End date, first. Then we'll come back to typing and we're actually gonna change the input here.

Instead of a dropdown, we're gonna use something else. In order to filter this by these dates, here's what we need to do. We need to add a two filters. So already we have only the ones with an id. We add a column and we say, okay this actually we go db is gonna be B two two B 16. Is greater than B2 here.

Minus one. Okay. So what that means is we'll include everything with a start date. So you can do not the minus one and then you'll get everything in the between. So not including the start date. If you use greater than, you can also do greater than equal then actually. And we'll get everything, I think.

And that's, yeah, every fourth. , just double check. Let's bring that to June 10th and see those things that disappeared. So now everything is between June 10th and what is this? August 6th. So let's do if we select something like that, doesn't, oh, our end date. It's only doing one. So end date doesn't matter.

Let's do, let's just double check. Six 20.  and now 6 25. So this is greater than or equal to. We can then add the end date, which needs to be db same place. Db DB two to B 16 is less than or equal to. Sign is B three

and we get an error because, no, this, the end date is before the start date. That is good. That means it's working. So now we have 6 25 to eight 12. We have 6 25 here and all of the Julys. Great. So this works. So what we're doing is we're saying filter this database, which we can add anything we want over here for start date and ending.

Sure. God scared a little bit. What we then need to do, let's just color this so you know this can.

edited, and now we have start date, end date, and now we need the country. So we're going to use D. So we're gonna say DB D two to D 16 is equal to B five, so this is Mexico. And now everything other than Mexico shows up if we use. , now we have usa. And now I'm gonna take a short pause and I will be back.

Okay. So before I go through how to do this checkbox, because it actually is very difficult we have a situation where I don't necessarily know what the person I'm sending this to needs. Here's a situation that I have come up with. So basically if that, neither of these are. . If we have type Toyota and Nissan here available or something else and we just want Nissan, Toyota, if nothing is checked, then do not have any results.

If Nissan is checked, have Nissan. As a result. If Toyota is checked, have Toyota as the result. If both are checked, have all meaning, everything, Nissan or Toyota as an option here. That's what I'm gonna create. But before we go through that, I do wanna just double check cuz sometimes what you can do when you have data that you absolutely can move around.

Maybe there is some reason you can't necessarily edit it, but you wanna view. You can select it all and go up here to filter. This is create a filter. And now we can just click on these and select what we want. Actually, we don't, we don't wanna select the date we want. We can select by condition, value we can select this particular select or clear.

We don't want any. . We can also select by type here. This is actually a lot easier to do. We just want to uncheck the unknown. So we only want Nissan and Toyota and here we go. We see Nissan and Toyota. We can check one and we want sort of this functionality. See if we have nu neither checked nothing shows up.

If we have both checked. We have Nissan and Toyota. So we sort of want that functionality, but we also want.  Have a range of, of dates or, and, and select specific countries. So this filter has some options. And you can even sort by color or filter by color, you can sort plus filter. This has a lot of options.

But what's interesting is that sometimes, and at least some, some cases you do wanna have a, a filter that you design your own way and you have limitations and opinions in your own way. What this filter will do also, and I find this a very Why I don't use this filter almost ever is because by using this filter, anyone else using this document now sees this filter and is only able to see what's in the filter.

They must turn the filter off and then they can see the data and it turns the filter off for everyone viewing this sheet. So by building your own filter, , what you're able to do is, one, be opinionated, set things, how you wanna set them but also what you can do is create this filter on a separate, completely separate worksheet and give people different views into the same data.

Or you can even set the filter. And then duplicate this and set a different filter so you can give different views to the same data to different people. If you know, like, oh, I, if I, if you know, like I only wanna see this information, say I only wanna see everything from in the last seven days. You can do that with this kind of filter and then set a say, oh, only past week if you wanted to do that over here, sort of can do it.

But then this data is only available to see, be seen in that. By adding filters, you can see it in different ways. Okay, so back to this problem. So what I'm having a little bit of difficulty right now and, and hopefully this isn't si derailing, I will pause the video after explaining this and then I'll come back with some type of solution.

Basically what we're saying is, and I'll repeat it here, we're we're, we have four situations. We have, neither is checked, so we don't wanna see anything. We want Nissan checked, we only wanna see Nissan. We have Toyota checked, we only wanna see Toyota both checked. We wanna. , that's what we're trying to do in this filter filter.

I'll, I'll share with you a few things I tried to do if I've tried to do an array combining things, I'm getting a, an error when I combine two filters where one of them is unknown. So let's I'm gonna pause the video now and I'll come back with a solution or some type of hack solution in a second.

All right. So I think actually , there is a something else going on here because with neither is checked, you actually want, you don't want all you want, you do want all, if nothing is checked, you want every type. So we have some other types here, right? And we want, when both are checked, we want Nissan or Toyota.

I think when I went through this before I was on Toyota,  and nothing else. So right now, this is what we have. We have, if nothing is checked, when we are not filtering it by type we should have everything. So let me show you actually, if I have Nissan, there's Nissan. If I have Toyota, there's Toyota.

Let's set this. All the way there and yeah. And now if we check both, we have Nissan and Toyota, but we have nothing else. Let's add in. We have some other, we have unknowns here, right? So we actually want, if nothing is checked, we want everything. , I'm gonna fix that and I'll be right back. But I think this is now a better solution or, or the problem is, is now defined here.

If nothing is checked, give me everything. If Nissan is checked, only give me Nissan. If Toyota is checked only Toyota, if both are checked, we want Nissan or Toyota, I'll be right back. So let me explain this sort of if when we add some ifs together, and maybe that'll help me wrap my head around what I'm trying to do.

Cause I'm a little blank here.  Let me just make some notes. Okay. So what, what happens is an if function has three arguments, it has something that is going to resolve true false. And it's some argument. It's saying like, what's interesting is that it, if you insert a box somewhere insert check.

This checkbox is literally a visual representation of the values of true false. So what's interesting is you can use a check checkbox. This is true, you can see it up here, and this is false. So you can use a checkbox only here. You can say equals if C three, C three, and well. We're gonna have two arguments.

You're gonna have a true and then you're gonna have a false. And you're gonna say, you know, checkbox here. And if it's true, checked if it's false, not checked or unchecked. So what we can do is in our if function equals, if checkbox is c3, you can use that as the true false. And if it's true, let's just do that.

If it's f false, do that. And now see this is unchecked because it's going here, checked, it goes here, right? So there's three arguments, but we can string these together to get very a variety of things. So we can say, if we can start our if and true false, we're gonna use our checkbox true false. And if it's true, do another if.

And now inside of that true, true false. So if this is. Do an if true well true false, and then do true and false. And so now we'll end that. Let's just visually end that. So now this is one true false. Let me color this in orange. And this is another true false, but we only get to this true false if the orange is true.

And so we can say,  Orange. True. So we now have true, oh, let me not take that. Let's keep it here. So if this is true, then we're gonna go to this true false. So if True, true do, if this is true, and this is true, do this. If this is true and this is false, do this. Right now we have a heuristic, we have a path that we have to take.

We also have a comma. . Okay. So we have, and we can in place of that, also do an if and true false, and then we'll have true and false. So now in the case of this orange being false, we jump over here to this if statement and have another one. So we'll maybe color this. Let's color this. ? No, not that blue.

Let's try a different blue. This one. So we now say, okay, this orange is true. False. If it's false, go over here. And if this is true, go here and false. So we have, let's do, we have two options. Let's actually do it this way. I know what we'll do. We will say we have true. True. False. False. So this is going to be all orange.

Whoops.

And this will represent orange. And now we have true false and true false.

Two more shortcuts are going crazy right now. Oh my God. One second. All right, so we have orange. It says true or false. Once orange is true, then we'll have a yellow True false. If orange is false, we'll have then a blue. True false. So we have four situations here. True, true, true. False. False, true, false. And I think this gets us our let's go to our filter.

And I think that gets us our all of our options here. Nissan, all. Nissan. Toyota. Nissan. Or Toyota. So we have neither checked, which is false. False Nissan checked, which is true false Toyota checked, which is false. True. And then both checked, which is true. So we can map this very quickly. Let's me take this.

If we understand this true, false, we understand this. Alright, so this is going to be true. False is Nissan. This true false is going to be Toyota, but this is right. Toyota first. Nissan first, then Toyota.  and, and if you wanna add more, what happens is you have to actually double this and you have actually eight possibilities.

Again, I do not believe this is the only solution. There might be some very quicker solutions, faster solutions, but this is a solution I've gotten here. Okay, so this is actually wrong. This is the opposite way. Sorry. I just realized. , this and this. Let's move this here. True, true is Nissan and Toyota that's both checked.

Let's get this right first. And then Nissan checked only. Toyota checked, which is false. True. And then neither checked. Okay, so we have our filter already for everything other than this. So let's go get that again and we'll.  our filter parade. I'll say oh my God, these I think this is it. And if not, we'll we can add this again.

So, okay. First one we wanna know is Nissan Check. So if, and this is gonna be before, so if it's true, true. Let's build that first. So if four is true, if b. Is true then True. True is we want Nissan or Toyota. So we'll have that there. Let's put actually actually before we do this filter, let me save this filter somewhere and I'm just gonna do messaging.

We will do , or we'll call this or Nissan or Toyota, you can do this sometimes to just double check your if parade or if functions are are correct. So now we want true but false. So if B four is true and B five is false, meaning we have Nissan Check, we don't have Toyota Check, then this is only gonna be only Nissan.

Okay? And end that. But now we have the other side, right? If it's false, if B four is false, if Nissan is checked, then we need to know is Toyota checked? So if B five, same thing. Now we have a false true. So that means Toyota is checked. Only say only. So you would say Toyotas give it a little different Toyota only.

And then. . False and false. Then we want everything. We want everything. Okay. Let's end the parenthesis there. This says everything because both are unchecked. That's correct. Nissan is checked only Nissan. Nissan. And Toyota is Nissan or Toyota, Nissan. Unchecked. Toyota checked Toyota only. So now those are the only four possibilities here, right?

We have nothing checked, which will show everything. We have Nissan checked, only Nissan, Toyota checked. Only Toyota. And then both checked we have Nissan or Toyota. So now we know exactly that. Our if parade, let's call it, is absolutely correct. We just need to figure out what are the, what we put in the filter.

And what we can do is we will the very, the easiest filter, I guess is gonna be all. So where is all everything here? So let's do that. Cause we don't care about the C column at that point. So it's just gonna be everything and let's just uncheck it and double check that there's everything is that.

I don't think that's everything. Well, oh, it's everything based on the other  options. Okay. Then we want to add in let's do this, let's do Toyota now. So where it says Toyota only, we're gonna copy our filter again, and in the case of this, we will do five. And now if Toyota is checked, we only have Toyota.

let's do Nissan now. Only Nissan and we'll pace. And now our C column is c4. That is correct. And so now just that check. Now we have only Nissan. Now the only, now the last thing we need to figure out is Nissan or Toyota. Now how do we have a filter where we have both of those? Well, an a simple answer is that we put in curly brackets both of the.

And I'm gonna do that right now. This is sort of the simple answer. Let's just delete all that. Put in our filter, but put, actually no, don't put in our filter first. We're gonna put into curly brackets. And in those curly brackets, we're going to put a filter.  Semicolon, and then another filter. And we're gonna just change the C4 to c5.

So now we have two filters in there, and the curly brackets put those together. They, they, they, they create them together. So what happens is we found out earlier and I'm not sure if you absolutely caught it or if I showed you, but in curly brackets, if you put two things together and one of them has an error, the entire thing has an.

But in this case, we only get to the point of having them in curly brackets when we know it's completely true. Okay, so we have now have all of our true, true, false. False. True, true, false, false, true false, true false and false, true blah. And now we have our filter function. So we have start date, end date.

We have our type. We can say Nissan and Toyota or Nissan or Toyota.  and then we have our country. We can say, let's do say nothing because we have nothing. And now everything that has usa Yeah. So even that works if we change our country. This is really cool. So hopefully you enjoyed understanding, now you understand or or at least see an example of filter and a very complicated filter where we have a multi.

Types of, of of situations where we have these check boxes as well. We did start date, end date filter. So instead of just a filter ba based on exactly the date, we have an greater than or equal to the start date and less than or equal to the end date. And then we have our country filter is based on a drop.

we dealt with this sort of if parade of sorts. And if this is, if this is confusing at all, feel free to comment down below what your frustration and challenge is, and I'll try to make a follow up video based on showing you or, or clarifying that situation. Thanks so much for watching. We have created from a data.

We now have a filterable view of that database. And thank you very much. Bye.