Student Handout: Students can follow along with the instructions below, and modify the text for your own use later on. FILE to use: salary_data_states_corrected.xlsx Pay attention to data type (“Date”, “Geo-“, “Abc”, “#” – as dimension or as measure matters when Tableau tries to generate charts for you) please! When you help Tableau recognize the right data type, Tableau then will be able use the variable in its proper way. The data set is given to you as is, and it is part of our job to do necessary steps and prepare it for certain software. Histograms (Chart1) 1. Start a new sheet. Read in the data, and check to see whether “paid wage per year” is a measure or not. If not drag it to Measure. 2. Now double click “paid wage per year”, and from “show me” select histogram (in green for Tableau version 10.3). Click the "Label" icon on the Mark Card and check "Show Mark Label" to show frequencies. Change frequencies to “Percent of Total” using “Quick table calculation”. 3. 4. Bring “paid wage per year” to the Row again, use Count(CNT) (not the “Sum” by default). and then using “Quick table calculation” to change it to “Running total”. And then “edit table calculation” using “perform a secondary calculation on the result”, at “secondary type”, pick “percent of total”. 5. Right click on the lower chart’s Axis, and pick “dual axis”. 6. 7. Right click on the dots/bar for cumulative percentage, and change the Mark Type to “line” and keep the histogram of frequencies as “bar” (if not, change it to “bar”) . 8. 9. At the left hand side Data pane, right click on the variable “Paid Wage Per Year (bin)”, and “edit”. At “Size of bins”, select “create a parameter”, and then for “Allowable values”, pick “Range”, and use minimum “10000”, maximum “40000”, and check the step size, put in “10000”. Then OK. Now, underneath “show me” you will find your parameter. You can change it to “slider”, and play around. 10. Right click the first CNT(Paid Wages Per Year”) and choose “Clear Table Calculation”, so that your histogram is in Frequency, and the cumulative line is in percentage. 11. Bring “job title subgroup” to filter, and show filter. You can now pick the groups one by one and examine each. What can you comment on what you see? Boxplots (Chart2) 1. Start a new sheet. 2. Bring “paid wage per year” to Rows. Now you have the median paid wage per year (if you have change the default aggregation to “median”) shown in a bar. 3. From menu “Analysis” uncheck “aggregate measures”. Now you can select box plot from “show me”. 4. Bring “case received date” to Columns. Its default is year as dimension. You now have box plots by year [you may need to change the datatype of “case received date” to “Date”, so that you can request aggregation by year.]. 5. Bring “job title subgroup” to filter and you can now view the job titles one by one. 6. Select the Axis and “edit axis”. In “General”, choose Fixed, and set the range as from 0 to 300000. (how is this different from using a filter to set this range?). 7. Put “Number of Records” to Detail. What can you comment on what you see? Now let’s create a different boxplot, at aggregate level. (Chart3) 8. Start a new sheet. 9. Bring “paid wage per year” to Rows. Now you have the median paid wage per year (if you have change the default aggregation to “median”) shown in a bar. 10. Now bring “job title subgroup” then “work state” to Columns. Now you have the median paid wage per year (if you have change the default aggregation to “median”) shown by job and by state. 11. Now at “show me”, pick box plot. You can see that we have box plot by job title, and “work state” has moved to the MarkCards. 12. Put “Number of Records” to Detail. What can you comment on what you see? ////////////////////suggested answers to the above: Sheet 1 Observations. The x-axis shows paid wages per year ranges (the bins, with a size of $40k), while the y-axis shows the count of paid wages per year in each relevant range. The green bars represent how many people fall in that range in terms of their annual salary. The red line is the running total of % of all the people in this dataset. We can see that 2.96% of the people (out of 167,728 people) in this dataset earn less than $40K per year, and 50.96% of the people earned less than $80K per year, and 98.99% of the people earn less than $200K per year. Most people earn between $40001 to $120000, represented by the highest two green bars – one of them covers 80295 people (i.e., 48% of the people, earning between $40001-80000 per year), the other covers 59746 people (i.e., 86.68%-50.98%=35.7% of the people, earning between $80001-120000 per year). We can also set a filter to look at each job title subgroup one by one, and vary the bin size to see a more detailed distribute for salary [I suggest you use $10000 as bin size in your own homework]. Sheet 2 Observations. The median paid wages per year have remained somewhat steady between 2009 and 2015 for people in this dataset, but later years show larger range of wage distribution. The middle 50% percent of people did somewhat similarly with similar median wage at around $75k. Although median paid wage appeared to be high for year 2008, but we don’t have many data points there for us to make meaningful inferences. Sheet 3 Observations. These boxplots show median paid wage per year in different states for different professions. Note that each boxplot should have 50 datapoints to tell us the median wage distribution across states, for each profession. So the center point in each boxplot is the median (across states) of the median (across people in each state). From these distributions, we can see that attorney, data scientist, and management consults are among the highest paid professions. Across states, the median pay for these three professions are between $83k-88K. Teachers are the lowest pay profession in this dataset, with the median state’s median pay as $41478. And the middle 50% of states pay a median wage between $37956 - $44833 [these are slightly different from the upper and lower hinges, but close]. The median paid wage is the highest for attorneys in the District of Colombia out of all states and careers at $160k. Interestingly, attorneys in South Carolina are paid a median wage per year of only $46k, which is significantly lower (but it has only one record, therefore may not be reliable).
Purchase answer to see full attachment