Google Sheets - Dashboard Tutorial - Shared Filter Slicer for The Dashboard - Part 2

let's make another pivot table sales year to year so to do that we either needed a column that will give us the ear so we can refer to it or we can make a query function for that so I guess I'll just do the column here so change my select statement and I'm going to select select a-b-c-d-e-f-g and age those are the columns I'm going to need now if you're not going to use cost of goods you can skip that but you should do this before you start building your dashboard initially if you're gonna skip any columns so I'm gonna add another column and I want this column to be based on that column a which is the date but I only want to get the year out of it so I'll do a year a hit enter and you can see how we're extracted the year out of it in a column so now we can use that in our pivot if necessary I'll go ahead and use that in in a pivot table so data pivot table so values is going to be sales and then row label is gonna be here rename some things here I guess oops here and this would be sales so that's our pivot table I'm gonna highlight that copy go to my dashboard paste it right there or wherever it's supposed to show up then go back and delete this pivot table and you get the idea basically you keep just creating new charts creating new reports whatever it needs to be and you copy and paste them here and if you don't need it over there you just get rid of it and that's gonna be the logic of all the different modules we're going to bring on our dashboard the next step for us is to give user some functionalities so we want to be able to have this but we want to be able to filter this report so we want the user to enter the start date if necessary for the report and the end date and we want all of this information on this tab to update we will also want to give them option to filter by region which is this column over here so we'll just go ahead and create that so I'm gonna just copy this paste it here this is going to be region now over here we had that data validation rule checking if it's date we don't want to do that so we're gonna remove validation we're gonna do different data validation here create a data validation rule and we do list from a range now that is from the other tab move this so I don't need to start from the top I need to start from the region and scroll down so I have to click on this to be able to do that so click select hit reject input otherwise it's safe let's get back to here so our drop-down now has those unique options though the column doesn't have unique options it will figure it out it will just show unique options out of it so that's the region so we want the user to be able to come here and for example choose Midwestern and we want all of these numbers to update based on that and give us the new total we also may need the user to just leave blank we could also create something like all ears so it shows all if we want to be fancy we want to create another option here that says all instead of you know having them just click here and delete because they may not be able to figure that out so if we want to do that we'll have to do some additional steps which means that I'm gonna go under data validation and remove this validation and I'm gonna create some helper sheet I'm gonna do data validation list and now that I have this I'll go to the second cell over here and do equals unique that's the function I'm going to use I'll go to my temp data set and go from here I'm not including the region shift down and I don't want the end to be there so close here close there that gives me this right unique list the first item I'm going to enter is all I'm just gonna type it in the rest is gonna be the rest so that's gonna be my data validation list that I'm going to use so in my dashboard now here I'm gonna go data data validation and list from a range click go back to my tab and so I'm gonna go from here and scroll down so that if people are entering more we're getting those anyways I'm gonna reject input if it's not one of those options hit save and now we should have a new data validation box and that should be in our dashboard so click here all midwestern or this turn and so on and you can have all as a default option there so data validation list I can also hide that if I don't want that on the way so there we go that's our data validation so it's going to have this options and will have the person to choose one and it will give them our filtered report now how are we gonna get those filtered reports and that's where we start modifying the query on the step so originally we made this query to just get everything from the other tab so to filter our results we're going to actually filter this query to get the results we need in this tab so that the dashboard will automatically just grab those results and update so to do that we need to dynamically create the string of queries so for now I'm just going to copy the string itself encode escape I'm not changing it here go back here and just right click and paste values only so I have it here I'm gonna need this if I want to get everything so when this is all this has to be this but if somebody selects let's say midwestern how is this string going to be different so it needs to be this but then it needs to have a we're close and it has to say where the region column whichever one it was let's assume it's column B equals two and it's gonna select it equals two Midwestern something like that let's double-check was it column B no it was actually column C so I want to make sure or column C and by the way if these are not in the same exact order you should be checking the transactions tab for the column so the column C going back so that's what our query need to say if we want to filter this to Midwestern so let's just test it out and make sure it works so I'm going to copy it go back here and in here I'm going to just switch it up to this and now that should filter our data and see everything should be filtered out based on that now we have a little problem here in our chart this should not show up he because this is our grand total that's a little issue we'll need to fix we also have another problem here is that we are selecting blanks in our years which we didn't filter out apparently so that's all fixable solutions same problem here we need to make sure grand totals aren't a part of our calculation but other than that we're doing fine so let's fix this so there are this there's this grand total that it looks at it as a state that should not be there and there is also this problem with a zero here so let's start with this problem I'm gonna click on this scroll down and other filters I'm going to do a year and date this this is something we've done before I'm gonna remove blanks out of it here we go now the next thing is fixing this issue so what's happening is I have those sheets hidden so let me actually bring them back view hidden sheets and this one so it's referring to this range of values and it includes grand total as one of the values in those so we don't want grand totals in this pivot to show up and what we will do we will just click here and uncheck show totals and that should remove grand totals and I'm gonna hide that and that should take care of this problem over here well that was not the right one was it that was the wrap one but we had to take care of that anyways so we need also this one so view again I'm gonna go on their hidden sheets and this one is by state click on this and make sure we remove the totals out of that to go back now you will see when I filter it to Midwestern using this particular text it's now showing just our Midwestern sales on the map and pretty much everywhere else great so this works the problem is that I just manually went and changed this we need it to happen as the user goes here and picks different selections it should really just pick that particular you know result out of that as I did this I've realized we should also probably change the way we're doing this data validation and there isn't this happening if I just do our hidden sheets and bring our validation list when I did this unique I'm doing that unique from that temp data set and if we filter that it doesn't have those anymore so it doesn't have those extra values now this is actually interesting on its own you may have to go back and hit all and then come back and choose whichever one when you want which would work but later not all changes so right now I'll leave this to show you how this is gonna work if we leave it like this and then I'll show you the alternative way to do this so I'm gonna do a hide for us the problem is this we need this text to be dynamically generated based on what's selected in here to do that we need to make some if statements so the first if statement we're going to check if the selection here is all because if it's all there should not be any statements over there that create that string right so we'll do equals F and we'll check if this right here equals all then we want an empty string as outcome nothing comma otherwise I'm going to say it should say C equals 2 so I'm not using the where part and later on I'll show you why because we have multiple criteria here we also want to do dates I'm gonna say C equals 2 and basically I'm building this string that says Midwestern or whatever that's supposed to be so I'm going to say C equals 2 so that so it's an apostrophe and it's supposed to say mid-western and apostrophe again and coat again and close so right now it says o which is basically nothing I'm gonna go back and modify our query to get back to our full result set because we already know what we need to do so we have all the options here once it's generated there we are so this right now says nothing if I switch to a midwestern it says midwestern if I switch to Northeastern it still says Midwestern because what we said is that if it's all do nothing otherwise do Midwestern so otherwise what we're going to do instead of doing Midwestern I'm going to concatenate the text from here I'll do double quotes do some spaces because I want to split that part so I want that apostrophes but I want my text in the middle so I'll do end operator then this which is a two and then another end that's northeastern if I switch to a Midwestern that's what Western if I do two all it's nothing okay so let me switch to northeastern so that's one thing so we're able to get this if statement that was going to generate C equals this in the cell so the next thing we'll need to do we also want to be able to filter this by the date that the user enters

Channel: Learn Google Spreadsheets

Views: 108,153

Keywords: Google Sheets, Dashboard Tutorial, Shared Filter, Slicer

Id: Pd3aNRQTDR8

Length: 15min 6sec (906 seconds)

Published: Sun Mar 18 2018