Maybe not a direct answer to your requirements, let me start with a simple sample using parameter and LOD.
Select a treatment, and members treated with this treatment will be displayed.
Select a symptom, and members with this symptom will be displayed.
From your description, it seems that there are members with mulpitle symptoms and/or medications.
Thus the analysis will be more complicated.
The sample is just a cross table view of the data, and other possible visualizations could be,
- time series, showing the trend of different Sympton - Treatment combinations
- bar chart, showing the counts of different Sympton - Treatment combinations
Sample.twbx 30.9 KB
If you'd like to have Medications and Symptoms show up on the same row, this can be accomplished by duplicating your dataset. I've attached a Tableau workbook that has all of the following included in it (including report views) and the excel file I used to create it.
You can use an inner join on Member (from the first table) = Member (from the second table) and use a join calculation to set Group (in table 1) = "Symptom and Group (in table 2) = "Medication". I hid a few of the redundant columns and now you have a dataset where each Member only has one row with their symptom and medication information on that one row.
You can then drag your information in like this to see which medications were used versus the intensity and duration of a symptom. I created a calculated field to combine the Quantity and Unit information. Here's a view of what you can report at the symptom/medication level.
If you want to report at the member level, you can now simply bring your Member ID onto rows as well.
Again, all of this is in the attached Tableau file, using the attached Excel as its datasource. Let me know if you have any questions.
I can't replicate what you have done with my files. Should be something stupid and still looking at what can be the problem. Just to understand what is going wrong.
But about the solution in general it doesn't do yet what I hoped.
We collect a lot of different data, so there also treatments of medications that are listed for daily use or that are used to treat other symptoms. If I add a test medication for member01, that is taken the day after the attacks of Member01, I stlll see this medication listed next to the attack of the day before.
If I now add an attacks for that day in the excel. I see both days have an attack for member01, but both have 2 medications listed, the one from 10-11 and the one for 11-11.
The Testmedication on the 11-11 is not taken around the time of any attack, so it should not be listed.
What I basically look for are those attacks that have medications listed at the time of the symptom (or almost the same time) to know what Members do to treat the attack.
We call this abortive treatments and those we hope to get listed first. But we also have daily treatments (like meds you take every day) or occacional treatments (treatments we do a few times like therapy, not connected to any symptom directly)
Hi Lei Chen,
Thanks for you response. I've downloaded your file and added an extra attack, this one without any treatment. It gets listed as well.
Problem with this solution I see is that I have to know what combinations there could be. So in this one we compare clusterheadaches and Oxygen. But Sumatriptan is an other treatment and some even go runing outside as an abortive treatment. The timestamp is important in this one, what is done around the attacks and do we find relations.
Some extra info:
We have various treatments and what we hope to find are trends for best treatments. We have various types of treatments:
abortive treatments (those you use when an attack starts like oxygen, sumatriptan)
Daily treatments (those we use on daily base to have less attacks in general)
Occasional treatments (Like therapy, not daily, not used around an attacks)
We want to concentrate first on our Abortive treatments, but we don't necessary know what treatments are used as abortive so we need to filter them out.
What I finally hope to find is a way that we can analyze our data (now over 10.000 data-points and growing monthly) and find all Abortive treatments. Than we would love to see if we can find a difference in intensity or duration's of these attacks with treatment of without treatment.
I solved the issue you describe above about only wanting to see the abortive treatments by creating the following calculated field to use as a filter:
This returns true only where a member logs a medication (the second date/time field) within one hour of a symptom (the first date/time field). This timeframe can be as long or as short as you'd like by adjusting the number at the end of the calculation. By dropping this field onto the filter shelf and only showing where the filter = true, it returns the list of symptoms and abortive medicines that you're looking for.
See the attached Tableau file that includes the updated filter logic.
Symptoms and Medication.twbx 40.4 KB
Looks better, but something strange happends. When I open your example, I see the following information:
This looks almost like it should be, but what seems to go wrong is the oxygen on the 11-11-2018. It has listed Oxygen as well.
If I go to data source I get asked to update the file, so I select to the file from your previous example. I've added a few lines of data. But then the result in the pages changes:
What I see here is that all the treatments that a member used seems listed with every attack. Exception is the first line listed on the image of "By Member"
And could Cluster headache attacks without abortive treatments also get listed?
I've added in these screens 2 datapoints extra. Only Cluster headache attacks, without treatment. One for member01 on the 09th and one for member02 on 12th. The last one shows up in the result, the first one doesn't. If I change the data of member01 from 09th to the 11th, it does show up, but with Oxygen listed. If I change the date to the 12th I get both treatments listed. (I'll add a screen of the last one and the data)
What it seems is that it is now always shows those treatments that have been used in the past, not what they have used with that attack. This explains the adding up while changing the date.
Order Member Name Group Date Time Date/Time Duration Intesity Quantity Unit 1 Member01 Cluster headache Symptom 12-11-2018 03:36:00 12-11-2018 03:36:00 00:35:00 7 2 Member01 Cluster headache Symptom 10-11-2018 03:36:00 10-11-2018 03:36:00 00:35:00 7 3 Member01 Oxygen Medication 10-11-2018 03:39:00 10-11-2018 03:39:00 15 l/m 4 Member02 Cluster headache Symptom 10-11-2018 05:18:00 10-11-2018 05:18:00 00:22:00 6 5 Member02 Sumatriptan Medication 10-11-2018 05:18:00 10-11-2018 05:18:00 6 mg 6 Member03 Cluster headache Symptom 10-11-2018 12:04:00 10-11-2018 12:04:00 00:45:00 9 7 Member03 Oxygen Medication 10-11-2018 12:07:00 10-11-2018 12:04:00 15 l/m 8 Member01 Testmedicatie Medication 11-11-2018 12:03:00 11-11-2018 12:03:00 200 mg 9 Member01 Cluster headache Symptom 11-11-2018 12:36:00 11-11-2018 12:36:00 00:12:00 4 10 Member02 Cluster headache Symptom 11-11-2018 05:18:00 10-11-2018 05:18:00 00:22:00 6 11 Member02 Oxygen Medication 11-11-2018 05:18:00 10-11-2018 05:18:00 11 l/m 12 Member02 Cluster headache Symptom 12-11-2018 05:18:00 12-11-2018 05:18:00 00:22:00 6
This is a screenshot of the data I see as source. There are some duplications over there, could that be the problem?
1 of 1 people found this helpful
Oh I see what happened. I had the filter checking that the medication was within one hour of the symptom, but I didn't check to make sure that it was the same day.
I've updated the filter to this:
And now the viz looks like this:
I'm attaching both the excel file I used for this and the Tableau file as well.
Fantastic. This one almost seems to work perfect. I could even load my own data to it and get all my attacks that had an abortive treatment.
There are 2 things I see:
There is something going on with the intensity, some intensities are higher than 10, and thats impossible because intensity goes from 1 to 10.
In the result I see all symptoms, also my neckpain registrations. Some of these are also listed with the treatment of Oxygen. Thats I think because there is an oxygen datapoint on the same day, but not within an hour of the attack or use of the Oxygen
I do want to thank you a lot for your help. You are making a small group of Cluster Headache Patients very happy.
Thanks for including the data Rogier. Made it much easier to troubleshoot
And you're very welcome for the help. I love helping others with Tableau, but knowing that it's having a real impact makes it that much more special.
For the intensity issue, I just needed to change that from a measure to a dimension. I previously had the sum of intensity in there which was causing values to add together.
For the filter, the logic was a little tricker than I initially realized. It wasn't enough to say that the symptom happened one hour before the medication, because it was just looking at the hour values and not actually checking that they were within 60 minutes of each other (so if a symptom happened at 10:05 and a medication was taken at 11:45, the filter I had previously thought these values were tied because 10 is within 1 hour of 11). I also needed to make sure that only medications taken after symptoms were included.
Here's the updated filter
Here's what the viz looks like now. Pretty sure this is what you're looking for. You can see that there are some occasions where more than one dose of oxygen was taken after a cluster headache. Since they were both within one hour of the symptom, they both get included.
Attaching the updated Tableau file and the updated excel file (which used your updated data) that I used to create the extract.
Hi Rogier - checking in to see if my last post worked out for you. If so, can you mark my answer as correct? Thanks!
Thanks Paul, amazing work from my point of view. Now it works perfectly.
About the setup we have now. You mentioned limiting the result to medications. In fact, thats how the first question was setup, just to keep it simple.
We collect data about symptoms AND treatments. But as patient, we do more treatments than only taking medicines.
So we collect data about a lot of different categories, one is medications, other are vitamins, therapy, alternative treatments, food or drink trigger. Basically we try to collect all we feel and all we do to feel better.
So now we display those symptoms that have a medication, based on time of registration.
Could we change back so it displays any treatment, so we see every possible combination? And could we display those symptoms that have no treatment connected?
Can I send you a PM?
Yes we could definitely make that work.
I just followed you, I believe if you follow me back that we'll have the option to PM each other.