Building a dynamic Dashboard using AWS QuickSight

hey folks i'm carol he's angel from dataducers and today we're going to be building a dashboard to visualize copy 19 metrics using aws sbi 2 quicksite that's correct so by the way quickside if you're looking to save money on the bi implementation that you guys have at work or if you want to just to do it apply it for your own question is the way to go it's a very cost efficient uh bi uh tool based on aws uh services so if you're looking for save money and do pretty much everything you can do with tableau or any other uh your power bi r2 other quick saves your way to go so let's just see what we have for today [Music] okay folks so this is the doctor we're going to be building today we're going to be looking at uh some of the metrics related to the copy 19 across the whole world you're going to be able to track uh the the cases the debts the vaccination trends across the whole world since uh everything started on early 2020 and so very quickly what are we going to be looking at here so we have the total cases worldwide we have the total debts we have the percentage of debts uh are out of the total cases and we have uh two kpis here that are going to be tracking the last uh the variation of the trend in the last days that we have in the in the data set um this we're recording this on april 2021 so the data that we're going to be using today is going to be uh one month um more data than what we are showing here but just so you know this is going to be showing you the variation of the last two days in our data sets so on how it's improving or getting worse in the short term in addition to that we're going to be looking at some metrics on these two on these three visuals here that are going to be aggregating the data by my continent or country if you want to see this at a country level you can play with that that little drill down we're going to see a geographic distribution we're going to be plotting here in the map um the metric that we're going to be visualizing visualizing and we are going to see the overall trend of that metric here uh with with the with the real numbers in the representing the bars and a smoother trim in in the line which is going to give a better look of the trend overall so the metric that we're displaying here uh we're going to control that by this um drill down menu here and the reason we're doing this is because we may be interested in showing different metrics and we wouldn't want to repeat the visuals right for each one of them so by default we're going to be looking at total cases uh but if we want to see let's say total cases per million to see how everything moves considering the population and so on then you can change that and everything all these three visuals are going to update so you can see the europe here and the distribution by countries and and see at this level of detail which countries are the most affected by each one of these categories um everything is dynamic here so meaning that if you if you click on one data point here it's going to filter out the whole dashboard by that particular data point in this case europe everything is going to be updated by that we can also play with start and end date filters to to control the data that we're displaying here so if if we move this um i don't know we're going to let's let's use i don't know much from february to march and you see how that's going to change overall so that's one thing and on top of that we have these filters that are global are affecting the whole uh the whole dashboard including the cards and the kpis so that's why we have it at the top here so you can select the continents that you want to focus on the countries that you want to focus on and and that's what we're going to be doing oh and another q um very cool feature is uh actions that we're trying from from this map so let's say for example that you want to travel to i don't know spain you want to go on vacation to spain you can actually see from here set up a way that you can see the travel restrictions affecting spain at the point in time with this link that we can trigger to the cdc um website and you can see the restrictions that may be in place for each one of the of the countries around the world other cool features that we built here is that you can actually see the vaccination trend on on the different countries for example let's look at israel in this case which uh i think that they're one of the countries with more vaccinations per capita uh so if you want to see israel or any other countries this is going to work for everyone so but for for the purpose of demonstration we want to see israel vaccination data and for that we are going to navigate with different sheets in in the dashboard and we're going to see for israel the percentage of people vaccinated against the population uh and the population vaccinated and this trend that is going to compare the new cases strain sorry the new case strain which is the line versus the vaccination so you can measure the impact that the vaccinations are having on each one of the countries so that is um what we want to build of course the dataset has a lot more data that you can use that you can play with it you can create your own visuals but i think this has uh an overall picture of everything that you can do with quickside of course there is always more there are a lot of uh ai and machine learning layers that you can add here in quickside we are not going to be covering this today but we are going to be doing that in the future so if you want to follow up just subscribe to our channel because we are publishing content uh every day not only quickside but other other tools so uh before we begin to start creating these um angel uh do you want to talk a little bit about the data um sure sure that's a single point that you mentioned kyle so we're gonna be updating videos every you know every other week or every week uh specifically this data we're gonna be updating a video very soon that will allow us to know how we are how we can connect uh you know a daily feed to this dashboard so that way we don't have to refresh them manually every day uh we're gonna be we're gonna be uh joining the the all the data sets that that we are using in the back and including the geo the geographical uh data sets all what are we doing you know all of that and we're gonna be posting on the channel uh very soon so stay tuned you know click on the the subscribe button on the on the little so you will be aware of all the videos that we're going to be just trying you know publishing the reason yeah sure so about this data um the data source here that we have we're going to be posting the links in the description so you can get the the two data sources that we're using here which are free are probably available um it's the john hopkins university data set about covid which is widely used for across pretty much the whole world it's daily updated so you can connect to that every day and refresh it and on top of that we have a different data set that we're using here with a latitude and longitude geographic codes for each one of the countries so we're joining those two to be able to plot in the map and if you want to stay tuned we we can actually uh we're going to be to create a video as well to to show how we're doing that and how you can keep the data fresh as in donation so as you can see here we have two two data sources already created here uh this one which is uh an old one that we have from march which is the one that is using the dashboard that we just showed and this one which is the new one is refreshed today which is i think april 8th 2021 and that's the one we are going to be using today so let's actually create a dashboard so i have my data set here and i'm just going to create an analysis from scratch um quick site before creating a dashboard you have to create an analysis the analysis is kind of like your playground where you're going to be playing with the data creating your things testing your things out and once you're ready then you could use the dashboard and you can share with with other people across your organization okay so let's begin let's start by building the first card that we want to see which is the total cases so here let's find the field that we want to see which is the new cases uh we can simply drag the field here we are going to drop it there and quiza is going to pick up just a card with the data which is what we want in this case um but we're going to format this a little bit um we don't want to see the whole number so we're going to format here um just if you know if you don't know if you select a visual you can open this um panel right here which is the fields well and there you're going to however you measure your dimensions and you can you can um change um some configurations in there of the field in this case i want to change the format um i want to see that that number as millions with two decimal places in this case just to make the card a little more smaller right that's it so i have my total cases uh globally no filters nothing is applied there so i'm going to rename this and there i have my first card very very easy very straightforward so then i'm going to create a new one i can either go come here and add a new visual uh repeat but since i have these of the size that i like and you know the overall feel um i'm just going to duplicate this because it's i'm gonna save a couple of clicks size in this and stuff like that so uh here i want to see the total bets so in this case i'm going to add the new debts field here and you can do a couple of things you can re if you step on top of the plus icons here you can drop it there and it's going to replace or you can simply just drag it uh over the field of welds and it's going to replace it as well i'm going to format these very quickly the same kind of formats i'm going to show this as million two decimal places um and that's it so i have what i want i have my total cases my total debts in this case okay and now i want to see because i want to have an idea in percentage of how many how what's the percentage of three millions uh how that is compared to two tall cases right so i want to see the percentage of debts out of the total cases i don't have in this data set anything to show that so i'm going to have to create a calculated field for that so let's do that if you click add here uh you have the options to to add a couple of things i'm going to be adding a new calculated field so i'm going to name these percentage cases and what i want to do is summarize my new depths see this autocomplete here which is very good so i'm going to summarize the new depth this is basically is going to do an aggregation of all the all of the new that column in the data and i'm going to divide that by summarization of the new uh cases let's find the cases here it is so that is what i want to see so let's save that and again i'm going to duplicate visual and i'm going to find this field that i just created it's right here you can see the difference of when you create a calculated field you see that you see it is shown with an equal sign uh and since since it isn't measured because the numeric field is going to be color in green dimensions text dates and so on are going to be color in blue so that's how you differentiate so i'm going to drag this i'm going to drop it over here to replace the field and here i have my my value now i want to see that as a percentage so i'm going to do the same trick i'm going to uh you see here that you can show this as number as a currency or some percent so this is a quick configuration that you can use and now you know that three millions is the two percentage of uh 138 138 millions uh so on so for every 100 people that get get scolded uh around two percent uh die so that's what i want so i'm going to rename this as um percentage that's cases and that's it so these are my three cards uh which are going to be informative on a quick glance so as soon as you look at this you're going to see your numbers here so now i want to do my kpi so i want to see how the trend is behaving in the short term so for that i'm going to add a new visual here and here at the bottom you can select the kind of visual that you're going to be using in this case you have a kpi a kpi view so i'm going to select that i'm going to resize it just a little bit and so now if you look at the fields well you have um three different pockets there right so you have your value you have your target value and you have your trend group in this case we don't have a target value because we're not going to be comparing to anything we're just going to be comparing the same metric uh for the trend group which is going to be the date so i'm just going to show you that i'm just going to find the value that i want which is the new pieces i'm going to drop it here that's the value that i want to to visualize in my kpi uh now how do i want to against what i want to compare well i want to see how that is changing over time so the time is the date that's my my date column in the data set so i'm going to bring that into the trend group and automatically this is going to do exactly what i want which is this is the default behavior right so it's going to find the data the latest two data points in the data set in the trend group which is date uh and it's going to find the latest two data points that i have which are the latest the latest two days a half april eighth and april seven in my data set and it's going to get the total of my value to for each one of them and it's going to do a difference between the two and i'm gonna have the different choice as you can see between in the in the last two days i had an increase of 158 cases reported which is not good uh that's not the journey you want to see but that's how it is so here there are a couple of things that you can do and you can change the aggregation period by default this day you can select any abbreviation period that we want if you want to see this by month for example you're going to have it by month of course we have a big difference because april is just starting so you know you can change the aggregation period in this case i want it by date you can also change the sorting volume by default is going to be from from backwards but if you want to see let's say instead of the latest two days the first two days then you're going to do uh from from an ascendant in this case right so but this is what i want now this is okay but let's see latest trend in cases uh this is fine this is good but this i mean you can see the number but sometimes you need to add a little pop on a kpi to actually uh represent in an easier way what you want to see so for that we will want to do something that is called conditional formatting and with this what we can achieve is actually um increase a little bit well not a little a lot more the impact that this is going to be uh having on the user visualizing this so for this we're going to to to do this kind of conditional formatting and what we want to do is add a color here to make this pop uh we're going to do it on the difference which is this value that you see here and in this case what we want to see is is if that value is less than zero we want that to be color in green because because that's going to be good so if your difference is negative that means that your cases are decreasing so that's good and usually in the kpis you visualize good behaviors uh a screen with using a green color and then we want to do the same but if it's greater than or equal to zero then we're going to show that in red because if it's greater than zero then your cases are increasing so that's bad you don't want to see that so that's going to apply that and well there you have it now as soon as you look at this you you know that something is not wrong here and you're right uh and that makes you know this way more easier to pick the meaning that you want to see there so that's great that's how i want it i you can also add an icon if you want i don't need to because by default this kpi is represented by the an up arrow of an upper arrow if it's if it's increasing or lower arrow if it's decreasing so that's usually good for me but you can play with that as well so let's do the same for uh that's so let's duplicate this visual let me actually make it a little smaller this one so this one over here okay so that's what i want so here i want to see the trend in depth so let's do that okay let me take did not not that's near that okay so now you see that the deaths are decreased which is good so i need to redo my conditional formatting here because i replace the the field so this i'm going to do the same very quickly so it's less than zero it's going to be green and if it is uh greater than or equal to zero it's going to be red let's apply that and now we have this the way we one-handed and it's not great it would be better if both were green but we can see the difference in this case so we have um an increasing rate of new infections but a decrease in rates in index reported so you can see the difference on the kpis how the color works and all that stuff so conditional formatting is actually something very useful so it's something that may help you a lot in your visualizations to help the user understand your data in an easier way so i'm going to read in this uh okay i'm done i have my four main cards and kpis at the top okay so now let's let's keep going let's add a new visual here and this in this visual i want to see the the trend let's say new cases by continent right so i want to represent that using uh horizontal bar charts so i select that kind of visual on here and in my fields well i have the three buckets that i have available for that so i'm going to find uh cases and i'm going to drop this in the value because this is the value that i want to visualize here and in the y-axis what i want is to see the continents right so i'm gonna put my continents there and this is what i what i see so what i see here is that in my continents uh the bars are going to represent the total cases in this case that i have so i can see that overall i have europe north america and asia being the top three then south america africa with a very few cases probably because of the population so uh that's good that's what i wanted but i wanna see this a little bit deeper so i want to see this by country so i'm going to put my location here as well so i'm not going to replace the continent because if i do this i will be only looking at locations but if i just drop this right below the continents you see that i have you see that i have a like a line there horizontal line and you can actually see that it says add drill down layer so if i drop it there what is going to happen is that you're going to see this arrow coming up here in individual and that arrow is a drill down option so by default this visual is going to be aggregating at the highest level that i have here which is continent but if i want i can drill down to the location in this case and i will be looking at the cases aggregated at a country level in this case right and i can i can go back up again and have that as i wanted so this is a very good feature because i can i can see different the data aggregation uh aggregated by different dimensions in the same visual and that is something that you're definitely going to use a lot so so this this is what i wanted i want to see the cases by country right but let me change a little bit the color of this chart because it's kind of okay so i like that color i'm gonna keep that so okay now let's let's add them up let's add the map to to the visual so i'm gonna i don't know the visual here i'm going to format it like like this and this is going to be a map visualization so the map i have field map or i have points and map in this case i'm going to use points and map so um when you see here the fields well of the maps visual you see that you have a geospatial bucket there so there we're going to move our coordinates so at the time of recording this video quick site doesn't support geocoding outside the us with country names so in the data set i have the country names but outside the united states quickside today april 2001 they don't support plotting uh geographic locations by country name i think they're working on that so they're probably going to be releasing that soon but at this moment the only way that you have to plot points geographic points in maps is using coordinates so that's why we need this piece of uh data um and we we have that in in our data set it's not in in the original data set that john hopkins university provides but we were able to get this data from a different public source and do the join before uploading the data to quicksay so as we said earlier we're going to be posting these two data sources in in the comments and then in the description if you want to to do that by yourself uh so we we have all the data points that we have represented in our map so i want to size this by new cases um just to to have the same level that i have in in this visual right here so i'm going to bring the new cases to the sizes and i'm going to change the color so it match the color that i have here which is this one i think yes so now you see a different picture now you see this by country and you have your united states which is the country that has the most cases reported then you see maybe brazil india russia and you can you can see the others as well and how they differentiated sets right so um we are looking at this and we we're kind of complementing the the visuals that we want to see right but sometimes this is not enough because i am looking at new cases but new cases is not necessarily what is really going it's not going to tell you what is really going on here because yes united states india brazil and russia are countries with the highest count of cases but those are one of uh i mean those are the countries with largest populations as well uh in the world well if it doesn't come china but um [Music] we want to see how that changes if we play around with it with the metrics that we're visualizing here so we are visualizing new cases but we we would like to add more and we will we don't like to create like four different uh set of the same visualizations to to to visualize different metrics right so what if we add something that we can change what metric we want to see and that is going to automatically update our um our visuals let's do that let's just do that so we want to see the new cases we want to see the new cases by million because that is going to tell you really which countries are performing better than others because then you you get the population variable here we're going to see the new debts and the new that's by million and for that we're going to first create a parameter this parameter we're going to name it um [Music] uh metric selection uh this name is just for you to know and link you're not going to be seeing that anywhere it's going to be a string data type and it's going to be a single value because you only want to select one so you don't want to have a multiple selection there so the static value which is the default one we're going to say that this is going to be the new cases and we're going to create that parameter and since we have the parameter we're going to add a control and that control is the one that you're going to use to change the volume that you want to see so we this one we're going to name it metric to display and it's going to be a drop-down list and the specific values that we're going to have there i'm going to find those and paste them just second or this one right here so i want to select between these four metrics and i don't want the ability to select all because i just want a single selection in this case right so i'm going to add that control and it is going to be pinned here at the top and this controls uh space here i'm just going to take it out of there because that is not going to be a global filter and at least myself i want to keep the global filters up there only so i'm going to move this to the sheet so it becomes a card and i can place it wherever i want in my dashboard so now by default we have new cases we have total case per million we have total debts and we have total debts per million of course changing that is not going to do anything because i still need to do something to to and to link this with my visuals so for that i have to create a calculator field and let's get there i'm just going to add a calculator field i'm going to name this one metric to display and this calculator field is the one that i'm going to use in my visualizations so how is this going to work i have to set up this so that when i change the value of the parameter that i just created it's going to update the calculation that is going to be taking a place in this field so what i'm going to do here i'm going to do an if else calculation which is the the conditional uh one of the conditional functions of quicksite uh and it works similarly to what you can find in in excel or in dax so you're going to have you're going to be setting up different outcomes that you can have so what i want to see here so i want to um depending on the selection that i have in my parameter i want to i want to interpret one metric or the other so this is what i'm going to do i have my parameters here i i have a reference to my fields i have a reference to my parameters here so i'm going to find my parameters and i'm going to say okay if my parameter equals total cases then you can and then you don't have to write it then it's just a comma then the metric that i want to see is new cases again if my parameter equal total cases per million then what i want to see is new cases per million then if my parameter equals total deaths then i want to see the new day and since i only have four options for options to select out of my parameter i don't need to specify the fourth one because it's either one of these three or the other ones so if it's not any of this one then what i want to see is the new that's permeability which is the other option that i have here so this should work for me uh let's see how it works let's save that and now instead of looking at the new cases here see i'm looking at new cases let's use that calculated field that we just created and let's see how it looks so let's let's find it it's called metric to display where is it here there it is so let's drag this just drop it on top of here and nothing changed because i have to also total cases by default but let's switch it to 12 cases by million and now you see that the picture changes right now we see europe uh let's actually bring this up here to the map as well so we can see that in the map let's replace this field and now we have it so now if if you remember if we're looking at the total cases you you see the united states russia indian brazil the worst countries the one with the highest cases reported so if we look at the total cases per million then the picture changes it changes dramatically right so now you see that there's a big cluster here in europe and you can actually zoom in and you can see okay you have um you have italy you have um france spain and other countries that has a lot of cases reported compared to the total calculation and that's exactly what i wanted to see without having to repeat the same visualizations for each one of the metrics so let's test the other ones total depth now you see it's similar to today the cases the countries where you have the highest population has had some more debts and total deaths per million and you see how the picture changes as well so this is working as i wanted that's great we're making progress here so what else can we do here okay let's see how the trend is right so let's add a new visual and this visual i want to be [Music] of a bar chart with lines so i want this kind of visualization here a cluster bar combo chart which basically what it's going to give you is space for you to visualize two different metrics one using bars one using lines across a date range so let's do that let's bring first let's bring the date let me deploy these fields well let's bring the date to the x-axis because i want to distribute this by date and then in the bars i want to see this new metric that we created let's put it over there so remember this is going to change accordingly to what i have selected here right so in this case it's total cases what i'm looking at and in the lines i want to see i want to see the same thing but i want to see a trend without all these spikes and valleys you know i want to reduce a little bit the seasonality that i might see here because for example we're talking about new cases we can in the weekends people probably don't uh tests as much as they do in during working days so they're probably less cases in weekends i don't know but that's maybe the reason why you see so many spikes and valleys here so i want to see this smooth around a little bit but without losing this right this detail so how do we do that so we have a field in this data set which is the the case is smooth uh but you know what let's let's create um let's create a calculation a calculated field to do that because um why not let's let's do it so we're going to add another calculated field and we're going to do a moving average movies in average um i mean if you're looking at this you probably know what that is but mobile average is roughly a um a calculation that is taken uh looking at one particular data point uh and is calculating an average of the n periods before or after or before and after and it's doing an average of of all of those values to give you a new value and that is going to reduce the impact of seasonality that you may have in the data and you should see a smooth line uh or whatever you're represented there so um for that we're going to use a function a table calculation in in quickside which is called uh windows average this one right here and it's if you look at the description very quickly uh it's explaining that right there right so it's average of of of aggregate of the measure that we're looking at for n periods before and after uh the data point we're looking at so this is the syntax this is where we're going to put it put in there so we have this is the function right the first um the first thing that we're going to be looking at there is the aggregation that we want to see in this case it's the sum of metric to display which is the calculator field that we created right so that's the first thing we want to look at this the sum of that metric that's my aggregation then i want to sort that by some attribute in this case since i'm representing the data across the the dates across the x-axis which is two days i'm going to just put here the date and i want to see that as ascending because i want to see from the uh late to the oldest day to the latest so a regular fashion and then i want to see the the star index and my md index this is the number of data points that you are going to be looking before you the data point that you're calculating and after so if you want to see one week and one one week before and one week after and doing that calculation so all i need to do is just seven because the aggregation that i have is days uh you have to play with that as well if you were aggregating this by month then you need to consider that because this is going to to use the same aggregation period that i have in my x-axis so basically what i'm doing here is saying okay i want to calculate a moving average of my metric to display uh by date ascendant and i want to look at seven days before and seven days after the point any point i'm at days because i'm aggregating my days right so i'm going to save this you see i'm aggregating by day here you can change this decoration period you can change it to quarter month week whatever you want but depending on what you have here the your calculating your calculator field is going to be affected because that's seven it's not days it's not months is whatever you have here in this case it stays so that's what i want right so let's bring the field into the lines and see how it looks uh moving around here it is i'm going to drop it here and there i have it so as you can see this is the same exact data that i'm visualizing in the bars but smooth it out without moving our version 7 days of course you can expand this you can use 14 days 30 days as you want and that is going to change the outcome but for me is this is enough and so you're going to see the trend in the line the overall trend but you're also going to be at the real values in the bars so you can see your outliers in the data and you can explore a little bit uh there so that's what we wanted and of course if we change the metric that we are going to be displaying this is going to change as well because it is using that calculated field for everything so that's good that ends the first part of what we wanted to represent here so let me just before i forget let me change the names of these things so this is going to be [Music] the metric to display by country uh so my content country and this is going to be the metric to display by right so let me format this a little bit let me so you can in this gear icon you can enter the options so i don't like to to see the label of the yeah i want to see the label what i don't want to see is the title because i mean it's pretty straightforward and in the x-axis i don't want to see the title either so there you have it uh it's looking a little bit better in here i want to i don't want to see the the subtitle i just want to give the title there and that's it for now okay something i don't want here is i if i hover over um first of all i don't see the country name i'm i mean i'm i'm very bad at geography uh so i would like it would be helpful if i look at the country name there and also i don't want i don't care about the accordion is nobody is going to care about that unless you're i don't know a topographer or something so let's let's let's fix that tooltip uh and this is how you fix tools right new and you have not shown here an option here for tooltip whether you can show it or not if you want you can actually play a little bit with the information you have there so um there i don't want to see the latitude i don't want to see the longitude so i'm going to hide those and i want to add a field i want to add the country name here which is the location in this case location so my level is going to be country uh my value is going to be a unique value if multiple values we're going to show a star i'm not going to have multiple values here in this data set but you might you may have if you're doing a different project so keep that in mind so i'm going to save this and now i there is the country and the value so i'm going to move this to the top and that's better that's much better so cool [Music] okay that's great so let's do something else let's add a filter here so because maybe i'm i'm not interested in the whole data set i'm just interested in one particular date range uh and i would like to filter by that uh the visuals here right so let's let's do filters let's let's add filters here so uh filters as you can see if you you come here to footer paint i don't have anything but let's create one um okay i need to select original first sorry i'm going to create a filter and this one is going to be by date right so i have a lot of ways to configure this in this case i'm going to filter by uh data time range i'm going to do between and my granularity is going to be take i don't want hour i don't want minutes because i don't have that so i'm going to include stardate and end date i'm going to apply oh i have to select the default so my default is going to be let's say march 2020 when everything started globally speaking and my end date let's say it's going to be today which is the ninth but let's see it's the eighth which is the latest data point so i'm going to apply that anything change because that's the data that i have and now i'm going to add this to my sheet and it's going to be added there as a card i'm going to move around a little bit and since this filter is affecting or i want it to affect these three visuals i'm just going to put it here inside this one and rename this as date now right now this filter is only affecting this feature right but i wanted to uh to affect the other these two as well so here you can change that over here so you're gonna only have it to affect this visual some visuals all the visuals and data set all up a couple of visuals in this case i want only some visuals by default of course you can just select that one and then i'm going to do a metric to display by country and the sum of matrix display i haven't renamed that yet so but this is the other one and now if i play with this let's see this data only for 21. you see everything change here here and here and let me change this try it okay okay so that's it i have my filters i have my parameters to modify the the visuals that i'm looking at um and that's it but let's let's do something let's do add some global filters right filters that are going to be optical two to the whole thing let's do that let's select any visual in order to create a filter we have to select a visual and let's add a global to the right continent in this case we're going to do a filter list is going to be include meaning that whatever having selected is going to be showing up and i want this visual to apply to all the applicable officials i'm going to apply that i'm going to add that to the sheet and in this case since this is a global filter i'm going just to pin it to the top so now i have it here in the controls pane and that's how i personally like to differentiate you know global filters to filters that are applicable to only one section of your dashboard like these two but that's an estimated uh stylistic approach so you can have your own approach on that so if i choose asia here everything is going to filter out by asia which is what i want all right so good you can do the same by country um i'm not gonna spend time on that right now it's pretty straightforward okay angel what do you think um what would you like to improve to to add next um what do you think you can play with actions that's that's pretty cool i mean just a little the overall view of the dashboard looks pretty amazing right so the fact that that that we will be able to actually play with actions or we click on the map and see what's going on in that particular country in terms of vaccination rates and uh on the actual um you know cdc uh restriction for travel that will be a huge plus and a pretty cool feature as well for the dashboard so let's let's go for it yeah man especially in summer summer is right there and i don't know about you but i'm i just want to go somewhere yeah that's that's right right so especially when you're trying to fly somewhere in the war and you know you don't know what's going on there you start planning your trip but at the end you don't know if you know how the restriction on that on that play so it'll be pretty cool feature to have it just everything in the same place so yeah so let's let's do that let me change which is the colors here just to to be consistent with the overall look there we go actually let's let's do it a different way let's put this one and let's change this to that greenish color and this one is going to be the light oh man okay i had it all the other way around so this is the one no okay nice trend and the bar is in light blue to see the details okay cool um okay let's talk a little bit about actions now what are actions actions are um so you guess actions that you can trigger uh from a visual in quick site the way you create an actions is you saw the visual that you wanna you wanna trigger the action from and you come here to the actions now there are different ways of create actions here different approaches you have uh custom actions you can create url actions that are going to be redirecting you to an external url um and you're going you're going to be passing parameters of that visual to the url so it's going to be dynamic right you're going to have navigation actions that are going to be sending you to a different sheet on your dashboard and you're going to have filter actions as well uh let's let's start for the filter action because that's the easiest one and quick side has this quick create option right when you click on that by default what it's going to do is going to create a filter action that is going to apply to the whole dashboard how does it work for example i just created that i'm just going to rename it okay not rename it from here just i'm just going to rename it like filter action remember this is the by default what you do with a quick action and the way this is going to work is you have your visual where your action is leaving so if you click south america that is going to filter the entire dashboard by that particular dimension right so you see south america there in the map south america only and you see all the stats for south america and between north america then it's going to change to north america and you're going to be able to see all the north american data across the entire views of course you can always uh edit that one and change the the the visuals that you want to to change on the scope and all that kind of stuff but usually you're going to use that a lot i mean i do i know i do and that's something that you're going to be using a lot the quick action that's why they're going quick right so that's a filter action you can create it the other way but that's pretty much the way it's going to work so now let's build the url action so and what i want is if i'm planning to travel to any other country i just want to see if there are travel restrictions on that country so it would be nice uh if we click on one country if from right here we can see that and there's a an easy way to do that and let's let's work on that so this is the cdc uh website um there are others but this is the one we selected it's up to you and the way this works when we look at the url uh let's change this to i don't know friends for example and you see it's going to change the front so this is working the same this way so this url this this part of the url is going to be standard for everything and the particle that is going to change is the contrary so if i'm able from quick side to to somehow give this particle of the of the url and complete it with the data of the country that i want dynamically then this is going to work and that's perfect that's exactly what i want and that's exactly what we're going to do so we want to trigger that from the map in this case right so i'm going to define a new custom action this i'm going to name it see trouble restrictions and this is going to be triggered from a menu option a select is the one that we just saw is just by clicking uh but we don't want to do that uh with a url action we want to trigger that from a menu so this is going to be a url action and here's when we're going to to see the differences and now you have an option to drop the the hyperlink and that's what we're going to use right so now we know that this is the piece that we are going to have to modify dynamic dynamically so how do we auto complete that then if we click here on the three dots we have all the dimensions and metrics that we have in this visual that we can use to play to to complete the url in this case we don't have the country in the world because we're not using the country in the visual itself we're using the tooltip but not the visual so we need to add the country detail to the visual before we can do this so i'm going to leave france for the moment here as a placeholder and i'm going to save my action so the new browser tab yes i'm going to save that so this is going to work only thing is that if i right click on any country in this case it's going to become where they can convert so i'm going to see the option there see travel restriction right but when i click on that it's going to send me to the front uh url which is okay because i only have friends so not this let's try to see how we can add the country to the details here and the the only way that i can think of is adding the country to the color bucket so my field is called location i'm going to drop it here and now i have the country there so you see it's location and you can see there that they're in the tooltip uh and because of i i selected a previously selected a sharp color if i reset that to default so you see now you're going to see that every single color is going to every single country is going to have a different color because i'm coloring by location but i don't like that i mean i i just want to see one color there so i'm going to revert that i'm going to select uh one color for the whole chart uh this one so that having the location there in the corner is not going to break my visualizations so here i'm going to remove the legend i don't want to see the legend right and i'm going to fix the tooltip again i'm going to hide my location and i'm going to hide this i don't know if this is showing up i thought i was i had it before now we got it so that's how i want it but now we have the location there it's not affecting in any way the visual is not affecting the tooltip it's not doing anything it's just there and since it's there we can use it in the actions uh let's edit the action now instead of friends now we can add the location save it and now let's go to india let's see india see to our restrictions in india and voila there we have it thanks so it's very bad so you probably won't won't be trump to india anytime soon but you can keep track of that if you do this right so this is useful now there is something that is going to break here and i know it because um i've been playing with this but let's go to summer arena for example san marino is it has a uh two particles name we have sand and we have marina uh so if we click on that and we see this rocket restrictions in submarine there you have a four or four ever so that that means that the url does not exist and the reason if you look at it uh closely is because you have a white space and the the web call is out of completing that and i'm pretty sure that if you add a dash there instead of the one blight space then you're going to see somewhere in the data right that's good but how do we fix it because we don't have the dash in our data well we have to do a little walk around there we have to create a calculator field that is going to uh replace that space for attach so let's call that location normal black spaces and if you're oops i broke this sorry and we go back so sorry guys so let's create a visual the calculator field and i was saying if you ever done some bi work if you work with data probably you will you have done a lot of work arounds stuff like that that's like our bread and butter so you have to think fast so now what we're going to do is we're going to replace there's uh in every permanent language you're going to have this you we're going to replace i'm not 100 sure what's the syntax for that let's look it up here very quickly so i have my expression which is location uh the substring which is where i want to replace and then the replacement which is going to be attach so i'm going to go to my locations i'm going to find my blank spaces i'm going to replace those with the dash so that's my new field and then i'm going to use that here instead of location i'm going to add the field let's do that let's replace that and then you you have to go to the tool team you you want to hide that in the two pin you don't want that showing up there it's going to mess up with that so that's okay but now if we go to the actions if we edit the action and instead of location we use that new field save it and we go to san marino now it's working that's what we want and i think that we can now angel we can now plan our vacations yeah that's right that's right right i mean seeing the map i'm seeing the current situation i don't think that we're gonna plan our vacation any years soon because everyone have you know a bunch of throttle restrictions so many sections right well you know what um we're getting vaccinations uh starting in the whole country so if if we can track the the vaccinations and the travel restrictions then we may have more tools plan our our future troubles right that's good uh so let's let's do that great uh let's so this is done i think i have everything i wanted here uh about filters navigation and and so on so i'm going to call a day for this i'm going to rename this i'm going to see the cases that's the name that this sheet is going to have and i'm also going to add ah sorry i'm going to add a title here and it's going to be copied 19 cases well just let's put it this way coordinating trends let's call it day that's my name so i have my sheet with my name all the metrics that i wanted all the filters that i wanted all the interactivity that i wanted to have there so i'm good with that now let's work on the vaccinations uh trend so for that we're going to create a new sheet we're going to call that vaccination trend okay so what i want to see here i want to see how the countries the individual countries are progressing in the in their vaccination efforts there are countries that are ahead of others for different reasons but that's exactly what i want to see here so i want to see not only um which are the restrictions to one particular country but i also want to see how that country is progressing into vaccination so to achieve that i just as a concept i don't want this dashboard to work as a dashboard individually i want this this dashboard this sheet to be a complement of the other dashboard also i always going to get here um redirected from the other dashboard so the data i'm going to represent here is not going to have a lot of sense globally but it's going to have sense if i look at it at an individual country basis so uh we will get there now for for this exercise let's simply create a first visual that is going to be a clown tag uh a workload is how quick i call that so there i want to see the locations and i want to see the um the vaccinations they sorry the yeah their vaccinations the total vaccinations people fully vaccinate right yeah i want to see the people that has three the people that have received the two uh shots or one whatever vaccine you're getting and that's what i'm going to see there but now here we're going to talk about a little bit about about aggregations um the people fully vaccinated i should not be aggregating that data because what's going to happen and for this you need to understand the data a little bit um every single day this data is going to report a different number a number that is ideally going to be is going to be incremental um so if you aggregate all that you're going to have a number that is way too big and it's not going to be real right so what you need to look here is at the at the maximum number that you have so the this is going to change and what this is going to do this is going to look at one particular country and it's going to be at the latest number at the and the number at the people fully vaccinated number reported on the latest date on your data which is in this case for united states you have 66 million vaccinated which makes sense uh so i'm going to keep that there remember this this this sheet is not supposed to have a lot of medium by itself so keep that in mind i'm going to uh i'm going to change hide the subtitle i'm going to name this um i'm going to hide the title as well i don't care about tyler in this case so then i'm just going to duplicate this to to keep the size and now i want to see a kpi this kpi is going to uh compare how many people uh have been vaccinated fully vaccinated and is going to compare that with the population of that particular country so for that i have my value which is the people vaccinated and my target value in this case is going to be oh i'm going to remove the location from the trend group here because i don't want that so i want to compare versus the target value which is going to be the population population the populations happen something similarly which i this is something that i cannot aggregate i cannot sum so i'm going to look at the maximum value right so now i have my countries here and i have my how many people in the whole world have been vaccinated already against the population so less than five percent have been vaccinated our entire population so i'm going to resize this a little bit too to see the metrics at the top and then i'm going to to to create another visual to see the actual trend so i'm going to add a visual that is going to be uh showing the the the real trend and the trend that i want to see here oh this is going to be a bar and line uh kind of visual and the things that i want to see here is first i want to represent that by date um in the bars i want to see that the people uh vaccinated so that it's going to i'm going to see total people fully vaccinated right and in this case this is going to be well i can go both ways be consistent let's do with the max because this case is not going to matter much because i'm breaking this data by day by just to be consistent just get a max and now i want to see the trend of the new cases why because i would like to see how does the vaccination trend affects the cases behavior so for that i have a field in my in my data set instead of creating uh moving average like we already did that so we don't want to do that again so we're going to to use what this uh data has which is uh new case is smoothed here and i'm not really sure what's the moving average they do they use in there there's we're going to be posting the data dictionary that is provided uh so if you're interested you can study that and know exactly what what's the the calculations are doing that but i i really don't care much i just want to see the overall trend and how it works so i'm seeing that at the trend of uh the vaccines that is going to i'm just going to show in this dark blue and and the new cases i'm going to represent that let's put it in this light here to be consistent with the red with the rest of the dashboard right and actually i'm going to create a filter here by day because it doesn't makes a lot of sense to to see everything from from 2020 because vaccinations started early this year so i'm going to say here um relative dates i'm going to select year this year this is the other way i can play with the filters and particularly with dates uh i'm going to link that to the current date and time so i'm going to apply and there i have so now i see the trend it's easier to see and i see that despite the the vaccination is ramping up the cases are not quite decreasing yet there is a little bit of a spike going on right now let's see how long that lasts so let me do a little bit configuration here let me just move the labels sorry the legends to the bottom now to the top right there the x-axis i'm just going to leave it like that you can play with it and this is going to be the vaccination and cases right good so now what is going to happen or what i want to happen is that every time i when i'm here and i'm i want to focus on one particular country i want to see the vaccination trend of that country so what i need to do is create a navigation action that is going to to navigate to that vaccination sheet in the dashboard and it's going to pass the the country as a filter right so there are a couple of steps involved there uh first one is that i need to create a filter in this dashboard by country uh and i'm not well not by country by par using a parameter so that's the first step actually so i'm going to create a parameter first that is going to be uh i'm going to call it country selection going to be a string type is going to be a single value i'm not going to define any static default here so i'm going i'm just going to create it and i don't need to create a control what i'm going to do is set up my action now so i'm going to go to the visual that i want to trigger my action from i'm going to go to actions i'm going to add a new action this one i'm going to call a cd mission trend it's going to be on the menu option it's going to be a navigation action and my target sheet so meaning the sheet i'm going to be navigating to is going to be this one and now i need to link a parameter to that so the parameter is going to be the middleman is the one that is going to hold the value from from the country here it's going to pass it to the other sheet right so that's what i created the country selection parameter and now i have to i'm telling quicksand okay use this parameter and assign the uh a field to that parameter a volume to that parameter which is going to be this one location um with no blank spaces which is the one i created calculated so that's it i'm going to add that and to save it and now you want to see for example russia uh i see i now have the option to see the vaccination trend now this is not working right now because yes i'm moving here i'm passing the parameter but i'm not filtering by that parameter here and that's it that's the third step so what i'm going to do here is i'm just going to add a filter by um let's country i don't remember the blanks but location look like this one which is the the field i'm using i'm going to say okay this one is going to be a cost um a custom filter and this custom filter is going to give me the option to use a parameter so i'm going to do that by default when you select a parameter it applies to all the visual datasets of course you can change that but that's exactly what i want here so i'm going to select the parameter that i have which is country selection that at this point is going to have that value assigned i'm going to apply that and now you see how it works and and this is why i i told you the beginning that there's i'm not going to have a lot of sense looking at this dashboard by itself it's just going to make sense if you look at it when you navigate from from here because the only purpose of this is see how that is working at an individual country level so let's see how it looks for the united states there you see so in the united states 20 percent half of the population has received the the vaccine and that's that's very good that's very encouraging so you can see the vaccination trend you can see the case strength uh it's kind of flattening out it squeezes a little bit later these days but the vaccinations is trending upwards and that that's a good sign uh so actually let's see how it looks in countries that are doing very good job in in vaccination i think israel is one of the countries that at least at the beginning of the vaccination they started like looking really good on that so let's see how uh how that looks let's see israel see vaccination trends and there you have it yeah yeah and here yeah man and you see for them they have already fully vaccinated more than half of the population so and if you see the trend uh when they were here like at the probably 25 percent a place where the us is right now more or less the trend was kind of increasing but after that you see it's trending down and yeah hopefully that's what we see so let's let's hope that's that's that's a good sign at least so let's wait for the vaccinations to spread regardless so um i don't know angel um do you think are we that i'm missing something i think that that looks pretty good i mean i think that you also like that so it's pretty good it's pretty uh dense in the sense that you have all the data and all the the information that you may need all about copied over here of course you can create your own with data sets that will be providing the link again it's in the description but this is pretty much all you may need guys i hope you like it yeah and of course you can follow up we're going to be posting the links to the data in the in the description below so you can follow up and learn by doing which is the best way to to learn so let's wrap this up let's um of course you can always fix the labels the axis in this case for example you see that you have a dual axis here you can do customizations you can show a single y-axis and then you're going to to delete that i mean there are a lot of customizations that you can do you can change the way this is the layout and so on and there are a lot of things extra that you can do with ml and ai things that quickside provides we're going to be diving a little bit deeper on that in in other videos but for the moment we're done we're happy with this and this is something that you can actually show to to anyone pretty much could be interested in this kind of data so now the last step is uh share this analysis now the analysis as we mentioned in the beginning is the playground you play with the data with you lay out your rituals once you're happy then you're going to publish the latter and the way you do that is you share it uh you can publish the dashboard here you're going to name it copy 19 trend uh we should kind of publish it let's see how it looks so here you can share it as well if you have other users in your account if you are part of an organization you can share it here and we're also going to be talking about that uh in future videos about you know sharing and managing a large deployment of greek site but we're not going to be doing that for a moment and here is how your dashboard looks after uh published everything is going to work the same way did on on the analysis everything i mean i hope it does so it's i everything is going to play out just right so let's see bahamas as see the trouble so there's a problem here with bahamas we want to look at that that's something something maybe with a name or something like that so not to worry that let's make sure all the countries are working just fine we didn't mess up yeah we did it let's shoot something with the naming of the bahamas we just just need to look at the url and see what's going on there and let's see the vaccination trend let's see let's use russia and there you have it it's working it's working just fine and i think we cover a very wide uh i know set up of things you can do in quicksight there are a lot of more things that you can achieve uh but we covered filters we cover actions we cover some calculation fields um and a lot of things that are going to provide you with the ability to create something a huge soccer quickly further quickly and easy and if you're interested just subscribe and click on the bell so you get notifications every time we post new videos to this channel so yeah i think that's just about it right okay uh thank you guys uh we are data juicers uh because we do stuff with data thank you so much and we see you around [Music] you

Channel: Data Doozers

Views: 4,162

Rating: 4.9529409 out of 5

Keywords: quicksight, bi, business intelligence, covid report, covid-19 data, data visualization, data analysis

Id: MZGloAruLGQ

Length: 90min 27sec (5427 seconds)

Published: Fri Apr 09 2021