Dynamic Date Filters with Parameters (and more) in QuickSight

hey folks i'm carol here from dataducers and today we're going to talk about everything there is to know about date filters and how to use parameter to control these so for that i have prepared this very simple analysis it's just a line chart showing the weighted revenue by day broken down by region and down here at the bottom there's a table a pivot table with dates region and the sales person by segment and the weight of revenue by each one of those dimensions this data set is created by default when you create your quickside account so everyone should have it there and it contains data from 2011 to 2016. so you can follow along use that one and try to replicate this on your end [Music] okay so let's take a look at how to create a filter by date in the first place i'm going to select this visual at the top i'm going to come here and add a new filter by my date column so you can see here we have three different type of filters that we can apply when we're using dates date and time range relative dates and top at bottom filter let's choose this one first because this is probably the one that you are going to use the list and it is pretty straightforward to understand so let's choose that and now we have the option to select if we want to see the top or the bottom and how many data points we want to see for example let's say that i want to see my five my top five days by weighted revenue which is the same metric that i'm visualizing here if i click apply then i'm only going to see five data points here and these are going to be the five days in which i had more revenue overall if we apply this filter to the other visual that we have the table down here is probably going to be easier to see let me collapse all the columns and we only have five days which are the top five days in which we had more revenue overall and this is what top and bottom if you want to see the five days with the lowest revenue then you're going to choose bottom here apply well you're going to see a bunch because probably all of these have zero revenue so we need to add a type breaker here and what this is going to do is going to add on top of the bottom five by revenue you're going to add another layer there to filter actually the five that you want five different values in this case we're going to use the date and we're going to do a count and now it's going to see the data's dimension meaning that each day is going to be a different number and that should guarantee you to pick only five so let's apply and now we only have five of course these can be improved probably this is not the best solution in this case but again this is a very generic example that we are using here and as i mentioned at the beginning this is probably the date filter that you're going to use the list but it is there and if you need it you can use it so let me delete this filter now and let's add a new one let's add a new filter by date and now let's take a look at the relative dates this kind of filter was added recently to quicksite and this is something that is very user friendly and there are occasions where you can use it so here the idea is that you or the user are going to be able to select the aggregation period the number of data points that they want to see relative to one particular deity usually the date that you anchor your relative day 2 is the current day today but in this particular case since our data here runs until december 31 2016 that's not going to be the case so for example i want to see 90 days in the past from my relative day so i'm going to choose date here and here i'm going to select last end days and the number of dates i will be able to define those here i want to see 90 days and here is when you're going to set your anchor date by default it's going to be the current data and because 99 percent of the time that's how it's going to be but for this particular example i have to fix that data to something else like december 31st 2016. so for that i have to add a parameter i do not have any parameter yet so i'm going to add a parameter that i can use to anchor my relative day to and i'm going to call it end date it's going to be of date time and the granularity is going to be date because that's the same one i have in my data set and the default is going to be 2016 12 31st which is the last data point that i have in this dataset i'm going to create that parameter and now i'm going to come back to my filters and i'm going to say okay use this parameter and that's going to be your anchor date and i need to set this up again so i want to see the last 90 days from my anchor date and when i apply now you see that this changed actually let me apply that to all the visuals so the table picks it up as well and now you can see that i have 90 days in the past from december 31st i can add this to the sheet to allow our users and ourselves to control this from here and here you are going to find all the options that are available in this kind of filter and this is why this is very helpful in some use cases because now you or your user can simply come here and say okay i want to see the 100 latest days and that is going to change if they want to see this by i don't know months the last three months it's going to work as well and because the nature of this filter being so user friendly you're probably going to use it a lot what happens is that in the majority of the use cases where you have people really looking in details at your data sets and your dashboards people are going to ask for the ability to select a date period a start and an end date so this is a filter that i particularly like a lot because it gives the user a lot of freedom when they're going to choose the dates but there are two major drawbacks in this one first that you cannot use the period that this state is going to output in the details for example let's imagine that we want to see in the title of this table because this label is so big it has so many details and it is all the way at the bottom of our dashboard which is probably in a real life scenario it's going to be a very large dashboard uh i would like to see the the dates that i'm looking at here and with this filter type i do not have the ability to add details to our titles or subtitles awesome tables i kind of play with the tooltips because there are no tooltips here and that is something that usually people are going to ask and also when you have business users that are doing serious analysis on your data in your dashboards they are going to ask most of the times for the ability to choose a date frame meaning a start and an end date period and this filter type is not going to serve for that purpose so let's go ahead and let's delete this filter let's remove the control from the sheet and let's add a new one a new date filter and now let's talk about the date and time range which is the third type and in a professional environment is the one that you are going to use the most and it's also the one that is going to give you more freedom and also to your users so they can be able to select a specific time frame they want to focus on so here you have multiple options whether you want to use a start and an end date after one date before one date or one particular date let's focus on this one the between here you are going to select the granularity we're going to leave day and here you're going to select two days that are going to be your defaults right so let's say that we want to focus on 2016 october 1st and including that one and 2016 december 31st is going to be my end date inclusive right so i'm going to apply the filter and that's the period that i'm going to see represented here oh i forgot to include the start date i thought it did now we have from october 1st to december 31st 2016. and if i add this to my sheet now i'm going to have the ability as well to select the period that i want to see let's say that i want to see from august to november for whatever reason and that is going to work and this is going to be the response for those business users who want to analyze one particular date frame but this is actually not going to solve the issue that we have where we wanted to add the details in the title of this table for that we need to take an extra step and the reason is because for us to be able to add details in the titles of our visuals we need to get those details from parameters and we are not using parameters this is just a straight day filter right there so the values that we have defined here aren't going to be accessible from the details that we can add to the titles and subtitles of our visuals but we can fix that because this filter allows us to use parameters to set i'm going to say yes here and that is telling us that the filter is going to be applied to all the visuals in our dashboard and when when we choose this option we are going to have the ability to select a start date parameter and an end date parameter so we have the end date let's add one for the start date okay so i have my start and my end date coming back to my filter here use parameter and then for the start date parameter i'm going to choose start date inclusive and the end date inclusive as well for the end date parameter i'm going to apply let me remove this control now because this is not going to work and now the control if if you notice now i don't have the option to add this filter to the sheet because the controls i need to add are those from the parameters not this one because now this filter is going to work in the back end depending on the values that those parameters have defined so i need to come here to my parameters now and add the start date control i'm going to name it start date it's going to be a date picker and i'm going to add this one as well okay here they are at the top now so i'm going to move them to the sheet because i prefer to have them down there so now i have the same thing that i have before with the filter but now i have the control for each individual parameter so if i change this to i don't know august first now it's going to work from august to december and if i change this to november 1st then it's going to cut off in november 1st because at the end of the day the filter that i have applied is using the value that are being controlled by these two boxes right here so under the hood it's doing the same thing just the way the controls are defined changed so great that's great that is going to allow us now when we come here to this table and we can add in the title actually in the subtitle and start date to end date and now we have the description here and it's going to be visible to everyone accessing this visual without the need to scroll all the way to the top and see okay which are the dates that i'm using here they're just going to be right there and that's the magic of using parameters because parameters can be reutilized as description and all that kind of stuff that is always very helpful now all of this is great but there's one catch on this approach the problem is that the default values that we added for these parameters for example 2016 12 31st and 2016-1001 those are going to be fixed every time someone opens this dashboard those are the values that are going to be there and maybe for this example that's not going to be terrible but when you have a dashboard that is getting constantly refreshed every day every hour or whatever your users will hate that every time they open the dashboard the dates are going to be i don't know for one month ago and they're going to have to change it manually so wouldn't it be nice if quickside had a way to easily create a dynamic parameter where let's say the end date refreshes to the current date every day well the good news is that there is a way to do that the bad news is that it is at least from my understanding unnecessarily complicated but it's doable so let me show you how if you know how to set up row level security in quick site then you have half of the way already behind you if you don't i'll advise you to go and check the videos that we have for row level security because the logic of setting up dynamic parameters is very similar to that one so let's get there and what's the idea so let's say that this data is current so that today is i don't know november 1st 2016 right and that's my latest data point but tomorrow on november 2nd i don't want to come here to my dashboard and see november 1st i want that my end date gets updated as the times goes by because of course if you create a dashboard today and you set up today's date by default in one year from now you're still going to have that date there and everyone is going to have to change that manually and you want to avoid extra steps for your users right so we have to find a way to get this data updated every day with the current date if you come here to the parameter options we see that we have this dynamic default option here and if we click on there these are the options that we have so first thing we need to create a data set and we need to add it to this analysis once we have our data set there we have to have a column for the user and a column with the default value and this is why i mentioned that this is very similar to the row level security because this data set that you require is going to set for each user or each group a default value for one parameter in this case is a date but it could be for any parameter that they have so let's take a look at that table here this is going to be that middleman dataset in my scenario i'm going to use the user to assign dynamic defaults so i have these two users in this case and for angel i want to set i don't know october 1st 2016 to december 31st 2016 and for karel i want it to run from september 2014 to december 2014. ideally this is something that you're not going to have in excel you're going to keep this in a relational database where you can actually set this in a dynamic manner let's say if you have it in sql your end date column is going to be your get date function for example that is going to return the current date and for the start date time you can use something like date ad where you're going to get the days and you're going to subtract 90 days from your get date and you're going to refresh this data set every day in quick site at the very early morning or you can have a live connection to that because it's very small and this is going to guarantee that every day the end date is going to change dynamically to reflect the current date and tomorrow is going to change and the day after tomorrow is going to change and this one is going to change as well having this is an excel is not going to be of any use because these are not going to change but for the purpose of demonstration let's use this but as i mentioned ideally you should have this in a relational database with a live connection or refreshing every day at 12 a.m or 12 30 or something depending on the way your users connect to your dashboards so these are the values that we have and i already went ahead and created this data set in quick site is this one right here and if we take a look at it what we're going to see is basically that my user angel is going to see by default when the he opens his dashboard from october to december 2016 and karel is going to see september to december 2014. now i have my users and i have my start date and end date default values there all i need to do now is come to my analysis and add this data set there still this one it is there right now and now if i come to my parameters for example i come to my end date i'm going to edit that one i'm going to set a dynamic default i'm going to choose the new data set dynamic date parameters my username is going to be username my group name i'm going to leave it blank because i'm not using a group i'm using users in this case and my default value is going to be ended a quick and very important note here ideally the way to set up this is not by user but by group instead if you have a small account with three users and i mean you get a new user every six months or something that is going to be okay but if you have a million to large organizations then the approach that i will advise you to do is to create a global group for all of the users and set up all the dynamic parameters by that group so that every time a user comes in you don't have to go to your database and update the table or the view and add an user that's going to add a lot of work to maintain this so in those scenarios create a group put all your users inside that group and assign this at the group instead so i'm going to apply this the value is set as you can see here i'm going to update and i'm going to do the same for the start date i'm going to set a dynamic default i'm going to choose my dynamic my username is going to be username and start it is going to be the column in my dataset i'm going to apply it is set update and this should work now if you remember what i had was the dynamic values for engine cattle every other user that i have in my account including my user this user is not going to see any of those values he's just going to see the default values that i set up here from october to december but now if i share this dashboard i'm going to publish the dashboard the parameters i'm going to share it with angel cattle now they have access to that one and if you can see this user the values that he has is october 1st to december 31st which are the defaults that i set up when i was creating the parameters are not dynamic because this user is not in my lookup table but if i log out and sign in as carol for example cattle is going to see from september 1st to december 31st 2014 and let's take a look and these are the defaults that carol is supposed to see september 2014 december 2014 and that is exactly what he is going to see here which is what we want still he's going to be able to change this and everything is going to work only the defaults are the one that are going to be loaded from that table that we just set up and let's take a look at angel now angel should see october 2016 december 2016. let's take a look and that is exactly what is defined for him and that's it each kind of date filter types have more details that you can explore on your own but i think that you have the tools now to get the most out of dates and date filters in your dashboards and very importantly you know how to work with parameters to make these dates dynamic which is kind of abstract and complicated the way quickside approaches this solution but this is really going to be a difference maker when you set up a dashboard for users to see thanks for watching if you like our content please subscribe so that you know every time we upload new content and let us know in the comments what you think about this thanks again see you around [Music] you

Channel: Data Doozers

Views: 2,704

Keywords: quicksight, aws, aws quicksight, date, date filters, filters, bi, business intelligence, data, data analysis, data prep, date range, relative dates, parameters, date parameters, dynamic dashboard

Id: aHOf4MU4VL4

Length: 25min 16sec (1516 seconds)

Published: Tue May 25 2021