I’m an intern at Fred Hutch Cancer Research Center and am specifically employed by HVTN (HIV Vaccine Trials Network.) I’ve recently hit a roadblock and I’m posting in hope that one of you has an answer.
First, I’ll explain some basics about HVTN and my project:
HVTN has participates in various clinical trials, and these clinical trials are known as protocols (and may be written in the database as Study Id.) Each patient enrolled in these trials receives a Patient ID which we call Ptid. We use the Count Distinct of Ptid (patient IDs) to calculate the number of patients we have enrolled. Each patient/Patient ID has an enrollment date, which is known in our database as Enrolldt.
Now, getting to my question:
Is there a way to create a calculated field that will count the number of Ptids (patient IDs) ENROLLED for a particular week? For example, let’s say we have 5 patients. Their IDs are 1,2,3,4, and 5. Patient 1 is enrolled April 1, 2018. Patient 2 is enrolled April 2, 2018 (the same week as patient 1.) Patient 3 is enrolled the following week on April 8, 2018. Patient 4 is enrolled the same week as patient 3 on April 12, 2018. And lastly, patient 5 is enrolled the 3rd week of April on April 15, 2018. I want tableau calculate that the week of April 1st, 2 patients were enrolled. During the week of April 8th, tableau should also report 2 patients were enrolled. And for the week of April 15, Tableau would report 1 patient enrolled. Hopefully you get the idea. How would I create a calculated field to accomplish this task? And is it even possible?
A packaged workbook is attached below which is Tableau desktop version 2018.2. Also, a picture of a snippet of our database is posted below to give you an idea of how the data source is formatted and what we are dealing with. There is also a dimension for the start of each week (a week starting date from the beginning of the protocol/clinical trial till the projected ending date.)