I work at Fred Hutch Cancer Research Center and specifically for HVTN (HIV Vaccine Trials Network) and they perform clinical trials (called protocols) around the world. I've been assigned a project that involves creating patient enrollment charts, but I've hit a dilemma and I was hoping you could answer my question.
I've attached a copy of the type of enrollment chart I need to create within tableau so that you may better understand my trouble. One of the columns in my chart is the number of patients enrolled (The measure named Enrolledcount.) There is another measure called "Expectedcount" which is the number of patients we were projected to enroll overall. The final column is the Cumulative Expected Enrollment count. In other words, this value uses the Expectedcount but uses a date dimension (named Week Starting Sunday) as a filter to only pull the Expectedcount data points from today's date backward. The final column is the percent of actually enrolled patients, over the Cumulative Expected Enrollment (the enrollment expected up to THIS date.) But how do I create this "Percent" Calculated Field in Tableau if I need the Measure of Expectedcount to be filtered by date? If you are confused by what the issue is, look at the second attachment (a picture of my Percent calculated field.)
As you know, the Cumulative Expected Enrollment count is the "Expectedcount" measure but with a date filter (using Week Starting Sunday) that leaves only the "Expectedcount" data from today's date backward. However, I need to find a way to make Tableau automatically update the date/filter as time goes on, so that as each week passes on, someone won't have to go into the sheet and change the range of dates in the filter. View attachment 3 to see the current filter I'm using to create the Cumulative Expected Enrollment.
There is another dashboard that I made that displays the enrollment count for the last 12 weeks of each clinical trial/protocol. So currently I'm using a date filter in which I choose the range of dates (last 12 weeks.) I have the filter showing so that viewers can manually select the past 12 weeks. However, I need to figure out how to make it update on its own, so that when another week rolls around or new data is put in, Tableau will remove the first of the original 12 weeks from my dashboard and include the new week/the most recent enrollment numbers. View Attachment 4 to see the style of the dashboard.
The Tableau workbook attached is Tableau Desktop version 2018.2. The dimension protocol stands for the clinical trial's name. The measure Enrolledcount is the number of patients currently enrolled. The Expectedcount Measure is the number we expect to have enrolled from day one till the last date in most likely 2019. And Lastly, Week Starting Sunday is the dimension for dates of all the Sundays within however long the protocol is expected to last.