Tableau Certification - Desktop Certified Associate Exam Guide - Hands On Questions Part 2| sqlbelle

let's finish off the walkthrough of the hands-on portion of the desktop certified associate exam guide please make sure that you have your workbook ready and connected to the sample superstore subset excel file referenced by the exam guide in part 1 of this video we walk through questions 4 all the way to 10. in this video we are going to finish this off and walk through questions 11 all the way to 15. let's get started question number 11 determine which state in the central region has the highest distribution of profits using inter-quartile ranges so for this particular question it looks like we're looking at a very specific region and we are looking to compare states based on highest distribution of profits and usually when we're looking at distribution and interquartile ranges that indicates that we need a box and whisker plot so let's filter this by central region first let's take all of our states the measure we're looking for is profit so let's drag profit over and instead of aggregated profit or the sum of profit we actually want to see the spread of individual profit values so in this case we're going to go to analysis and disaggregate the measure and from here we're just going to change the mark to a circle make the size a little bit smaller or a lot smaller and from the analytics tab we can drag box plot and in here visually we can already see that south dakota has the biggest middle box but we could also zoom into this if we go to worksheet show view toolbar and from here instead of automatic show on hover so in here we're just going to get a toolbar that allows us to zoom in let's zoom into this one area right here so now the differences are a little bit more magnified so the answer to this question should be south dakota and that's letter a question number 12 look at the sum of profits for each product subcategory which subcategory is 31 069 below the average profit across all categories in a previous question question number eight we created a calculated field for overall average profit we can use this calculated field for question number 12. so we're going to create another calculated field that gives us the difference so let's call this difference from overall average profit and this will be our profit and this is going to be for a specific subcategory minus overall average profit let's drag all of our product subcategories over we're going to find the difference and then here sort the difference i'll copy over the difference both to color and to label and also add a filter to the difference because we're only looking for something very specific so it's minus 30 000. and then here we can find the minus 31 000 for envelopes answer for number 12 is letter c question number 13 what percent of the total profit do the top 10 customers by sales represent there's a couple ways we could tackle this and i'll show you both ways in here we are looking for the top 10 customers and we're also looking at percent of total percent of total quick table calculation top 10 customers we can get by either ranking or using sets instead of a pie chart this time i'm just going to use a 100 stacked bar chart both will be able to help us answer this question so in this case let's start by creating a set from our customer id create set and we want the top 10 customers by sales so under the top tab top 10 customers make sure this is by sales and make sure that this is sum of sales right here click ok we are looking at the measure profit drag profit over add a quick table calculation for percent of total and in here i'm just going to set this to fit entire view let's drag the field with quick table calculation onto labels so we can kind of see what that number is and then take our set put that in color and then here we can see that our top 10 customers by sales represent 5.03 of our total profit another way we can do it which is a longer way but provides a little bit more insight will be to take our customer ids we're looking at top 10 sales so let's grab sales first let's sort this and then from here i actually just want to have a field that ranks the customers so i'm going to copy over sales and add a rank quick table calculation so in here we have quick table calculation rank i'm gonna put that right beside customer id and change this to discrete so we can see the ranking right there and then we can take profit make that into another bar and in here we can see the profit that was made by our top customers but what we need to do is to have a running total we need to keep on adding all of these profits so from here quick table calculation it's going to be a running total but this is not enough to answer our question we want to know what is the percent of total with quick table calculations we can add secondary calculations we can go to the edit calculation and from here we can add a secondary calculation secondary calculation type has to be percent of total and it's going to be table down instead of table across and from here i'm going to add the label for the percentage total and let's change the formatting so we can see at least two decimal places from here percentage two decimal places so an interesting thing here is our top nine customers by sales contribute to 5.07 of our profit but when we reach our 10th customer it looks like this has dropped down so from 5.07 it has become 5.03 which tells us we've actually lost money from our 10th customer so the answer to question number 13 is letter b if you are finding these videos useful so far please consider subscribing i'd really appreciate it question number 14 what was the moving average of sales in june of 2012 including six months prior and six months after we can create a time series graph let's right click drag order date and from here choose continuous month let's put sales we can add quick table calculation on sales we can choose moving average the default moving average only considers previous two points and next zero a long table across so we can change this click on the drop down on sum of sales let's edit the table calculation and from here we are going to change this to previous six values and next six values so make sure you have these two values change and to make it a little bit easier to find june 2012 i'm going to go to the analytics tab i'm going to drag a constant line and we need to make sure that we drop this on the month of order date and then here we're looking at 2012 and it's the month of june which is six once we have this in place we can see the value it's 188 hundred and fifty two the answer to question number fourteen is letter c question number fifteen create a histogram showing the number of sales using sales bins of a thousand dollars which bins have profit ratios or profit as a percentage of sales of more than 25 select all that apply in a previous question question number 10 we have created a calculated field called profit ratio and this is the sum of profit divided by the sum of sales we're going to reuse this calculated field in question number 15. in this question we are asked to create a histogram and this is going to be an equal sized histogram bin size being a thousand dollars equally across the board and this suggests we can use a built-in feature in tableau that allows us to create equal sized bins let's right click on sales create bin there is a suggested bin size of eighteen hundred dollars we're gonna change this we're going to set this to a thousand dollars this creates a sales bin discrete field we're going to drag that to columns and we are going to take our profit ratio drag that to rows we are also going to set the fit to entire view and from here we can add a filter to the profit ratio show filter and in here we really just want to take a look at anything that is more than 25 so the minimum value that we can put in here is 0.25 i'm also just going to copy profit ratio so ctrl drag profit ratio onto label so that we can see the actual values and verify it visually so we have four bins that match the criteria 7000 8000 11 thousand and eighteen thousand so for question number fifteen it's letter c for seven thousand d for eight thousand f for eleven thousand and g for eighteen thousand those are all the hands-on questions for the certified associate exam guide i hope you found it useful i hope you were able to find new strategies to use not just for your certification preparation but also for anything else that you're doing in tableau if you have any suggestions on additional topics you want to see in this channel please leave a comment below i'll see you next time

Channel: sqlbelle

Views: 3,493

Rating: 5 out of 5

Keywords: tableau certification, exam prep guide, Tableau Certified Associate, how to prepare for Tableau exam, Tableau certification exam, Tableau Desktop Specialist, Getting certified in Tableau, Practical Tableau, Hands-on Tableau, Tableau examples, Tableau walkthrough, tableau certification training, tableau certification associate, tableau certification course, tableau certification preparation, how to get tableau certification, Tableau certification questions adn answers

Id: D-VHGqMzBeo

Length: 10min 44sec (644 seconds)

Published: Mon Oct 12 2020