Create a new worksheet called Story Topics that lists the Story Topic labels in a bar chart, arranged in descending order. Feel free to play around with fonts and colors to get it the way you want it.
Assignment 5: Creating Interactive Dashboards
Part 1: Data Preparation
Download the Excel file Pew News Story Data.xlsxand the Assignment5_Part3_AnswerTemplate.docxfrom Canvas.
Populate the News Coverage worksheet with data from the Lookups worksheet using VLOOKUPs for each section that is ends in …Code. For example, SourceCode should be followed by SourceCategory and SourceName(Short).
Do the same for PlacementCode, Geographical Code, and StoryTopicCode.
The purpose is to ensure that Tableau has data that humans can interpret. Those SourceCode values are of little use to people.
Part 2: Create an Interactive Dashboard
Use Tableau to create the Dashboard to reflect major story topics and is filterable by Date and Source.Feel free to experiment with styles and colors of your choosing, add an image, etc.
The only requirement is that the user is able to filter major news stories by date and source (ie., newspaper, radio, etc.).
To complete this:
1) Create a new worksheet, called “Story Topics,” that lists the Story Topic labels in a bar chart, arranged in descending order. Feel free to play around with fonts and colors to get it the way you want it.
2) Add a Date filter (range of dates) and Source Category filter for that worksheet.
3) Create a worksheet called “Stories by Source”. Add the Datefilters, just like with the Story Topics worksheet.
4) Create a Dashboard with Story Topics at the top, Stories by Source at the bottom, and the filters Story Date and Source Category on the right side.
5) Set the filters so that they affect both worksheets in the Dashboard, and make sure you set Story Topics to be a filter for the rest of the Dashboard (i.e., turn on “Use as Filter”). Your dashboard should look like something like this:
When you uncheck Sources, the column chart should update as well.
6) Name your Dashboard “Story Topics Viewer.”
Part 3: Test the Dashboard
Test it out by choosing the date range of March 15 to April 11, 2012 and make sure Newspaper, Radio, Online and Television are all checked under Source Categories to start.
Use the answer template sheet to answer the following questions:
How many stories about crime appeared on the radio during this time?
How many stories about Health/Medicine appeared on television during this time?
How many stories about Government appeared in newspapers, online and on the radio during that time?
What is the only topic where radio coverage accounted for most of its stories during that time? (select each story topic bar to see the distribution of stories)
Every Excel grandmaster needs to start somewhere. In this chapter, you’ll learn how to create a basic spreadsheet. First, you’ll find out how to move around Excel’s grid of cells, typing in numbers and text as you go. Next, you’ll take a quick tour of the Excel ribbon, the tabbed toolbar of commands that sits above your spreadsheet.
You’ll learn how to trigger the ribbon with a keyboard shortcut, and collapse it out of the way when you don’t need it. Finally, you’ll go to Excel’s backstage view, the file-management hub where you can save your work for posterity, open recent files, and tweak Excel options.
Excel fills most of the welcome page with templates, spreadsheet files preconfigured for a specific type of data. For example, if you want to create an expense report, you might choose Excel’s “Travel expense report” template as a starting point.
You’ll learn lots more about templates in Chapter 16, but for now, just click “Blank workbook” to start with a brand-spanking-new spreadsheet with no information in it.