Year-over-Year YTD Daily COVID-19 Trends

Year-over-year YTD daily trends in reported U.S. COVID-19 cases and deaths. This Tableau dashboard uses a date equalizer to display a year-over-year 4-week lookback from December 15 to January 12.

Background

I developed this dashboard after a request at work for a view displaying the COVID-19 daily case volumes reported into our internal database during the previous four weeks. The internal client also wanted to see how the case volumes compared to the same time period in the previous year.

I thought this would be a relatively simple task. I found a tutorial for displaying year-over-year trends, equalized the dates and popped the Year in the color mark. I was dismayed to find that I could not get just the previous four weeks to show properly on the graph, and I had to manually sort the dates to get the graph to start at December and not January. I could not find any tutorials on how to fix this issue. I figured out a way through trial and error, and I hope that this post will help you if you’re finding yourself experiencing similar issues.

Gather Data

I downloaded a CSV from the CDC so that I could make a dashboard using publicly available data. I only used submission_date, new_case, and new_death. I renamed them Date, Cases, and Deaths. I loaded them into a new Tableau workbook.

Calculated Fields

Once the data was in Tableau, I created a calculated field titled Date Equalizer. The original version of this calculation that I tried worked by changing the year to 2022 for all of the Date dates. The original calculated field did not work for my purpose since I wanted to show just the most recent date (January 12) and the previous 4 weeks. The new version changes the year to 2021 for all December dates. The year changes to 2022 for all other dates.

Then I needed to make a calculated field to differentiate the color and size of the bars for the past 4 weeks vs. the previous year. When I made this dashboard at the end of 2021, I was able to simply use the Year, but this dashboard now contains data from 2020, 2021, and 2022. To start, I created a calculated field called Max Date. I set it to the most recent date in the data. In my work version, I use TODAY() instead since there is a live connection to the database.

Next, I created a calculated field called Year Color/Size

Another field that I thought would be helpful was a Year field that could be used to show in a tooltip. I created a Custom Date from Date.

Start Building!

I was excited - I finally had all of the fields I would need to start visualizing the year-over-year data. I dragged Date Equalizer to the Columns shelf and the sum of Cases to the Rows shelf. I added a Date Equalizer filter to include only 12/15/21-1/12/22. I brought the Year Color/Size field to the Color and Size marks.

I clicked on SUM(Cases) and selected Quick Table Calculations and Moving Average. Then, I selected Edit Table Calculation and set it to calculate the Average of the previous 6 days, including the current value.

To remove the stacked bars, I selected Analysis/Stack Marks/Off. I clicked on the Size mark to increase the size of the bars.

Clean Up

To clean up the view, I removed lines and borders, and I changed the date format on the axis to show the abbreviation of the month and the day.

I also duplicated the Date Equalizer field, named it Date Equalizer Tooltip and changed the default date format to show only the month and day. I dropped that in the tooltip mark as well as the Year field. I also made sure that both the SUM(Cases) and the 7-Day Moving Average of Cases were dropped into the tooltip. I edited the tooltip so I could hover over the bars and see the Date, the case volume, and the 7-day moving average of case volume reported for each day.

To complete the dashboard, I duplicated the sheet and replaced Cases with Deaths to create the same graph for COVID-19 Deaths and made 6 text sheets with the sums of cases and deaths for various time frames.