14 Replies Latest reply on Nov 15, 2018 5:53 PM by Paul Wachtler

    How to find combinations of labels

    Rogier Koning

      We are a small patient group that is trying to do our own research. We collect data about our symptoms (attacks) and our treatments.


      We are trying to understand what we can do with Tableau and to find a way to compare data.


      Our data looks like this:

      1Member01Cluster headacheSymptom11/10/201803:36:0011/10/2018 03:36:0000:35:007
      2Member01OxygenMedication11/10/201803:39:0011/10/2018 03:39:0015l/m
      3Member02Cluster headacheSymptom11/10/201805:18:0011/10/2018 05:18:00
      4Member02SumatriptanMedication11/10/201805:18:0011/10/2018 05:18:006mg
      5Member03Cluster headacheSymptom11/10/201812:04:0011/10/2018 12:04:0000:459
      6Member03OxygenMedication11/10/201812:07:0011/10/2018 12:04:0015l/m


      Member 1&3 used Oxygen while having CH attacks, while 2 used Sumatriptan. Timestamps are not always exactly the same.


      How can we find those attacks in our data that seemed to be treated with oxygen, or sumatriptan or any other combination? Sometimes combinations could also be various symptoms. Like Cluster headache can come with some other symptoms that people collect data about.


      Second question is if we can find a way to display combinations of treatments and symptoms we see looking at all the members.

        • 1. Re: How to find combinations of labels

          Hello Rogier,


          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

          - ...






          1 of 1 people found this helpful
          • 2. Re: How to find combinations of labels
            Paul Wachtler

            Hi Rogier,


            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.


            Screen Shot 2018-11-12 at 9.09.03 PM.png





            If you want to report at the member level, you can now simply bring your Member ID onto rows as well.

            Screen Shot 2018-11-12 at 9.11.11 PM.png



            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.




            • 3. Re: How to find combinations of labels
              Rogier Koning

              Hi Paul,

              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)





              • 4. Re: How to find combinations of labels
                Rogier Koning

                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.

                • 5. Re: How to find combinations of labels
                  Paul Wachtler

                  Hi Rogier,


                  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:

                  Screen Shot 2018-11-13 at 9.47.41 AM.png

                  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.


                  Screen Shot 2018-11-13 at 9.47.31 AM.png


                  See the attached Tableau file that includes the updated filter logic.




                  1 of 1 people found this helpful
                  • 6. Re: How to find combinations of labels
                    Rogier Koning

                    Hi Paul.


                    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.



                    Symptoms & Medication.png Symptoms & Medication-By Member.png


                    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:

                    Symptoms & Medication-update data source.pngSymptoms & Medication-By Member-updated datasource.png


                    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.


                    By Member-Attack without treatment.png


                    1Member01Cluster headacheSymptom12-11-201803:36:0012-11-2018 03:36:0000:35:007
                    2Member01Cluster headacheSymptom10-11-201803:36:0010-11-2018 03:36:0000:35:007
                    3Member01OxygenMedication10-11-201803:39:0010-11-2018 03:39:00 15l/m
                    4Member02Cluster headacheSymptom10-11-201805:18:0010-11-2018 05:18:0000:22:006
                    5Member02SumatriptanMedication10-11-201805:18:0010-11-2018 05:18:00 6mg
                    6Member03Cluster headacheSymptom10-11-201812:04:0010-11-2018 12:04:0000:45:009
                    7Member03OxygenMedication10-11-201812:07:0010-11-2018 12:04:00 15l/m
                    8Member01TestmedicatieMedication11-11-201812:03:0011-11-2018 12:03:00 200mg
                    9Member01Cluster headacheSymptom11-11-201812:36:0011-11-2018 12:36:0000:12:004
                    10Member02Cluster headacheSymptom11-11-201805:18:0010-11-2018 05:18:0000:22:006
                    11Member02OxygenMedication11-11-201805:18:0010-11-2018 05:18:00 11l/m
                    12Member02Cluster headacheSymptom12-11-201805:18:0012-11-2018 05:18:0000:22:006


                    This is a screenshot of the data I see as source. There are some duplications over there, could that be the problem?



                    • 7. Re: How to find combinations of labels
                      Paul Wachtler

                      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:

                      Screen Shot 2018-11-13 at 8.43.14 PM.png


                      And now the viz looks like this:

                      Screen Shot 2018-11-13 at 8.44.17 PM.png


                      I'm attaching both the excel file I used for this and the Tableau file as well.




                      1 of 1 people found this helpful
                      • 8. Re: How to find combinations of labels
                        Rogier Koning

                        Hi Paul,

                        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

                        By Member-Neckpain also with Oxygen.png


                        I do want to thank you a lot for your help. You are making a small group of Cluster Headache Patients very happy.

                        • 9. Re: How to find combinations of labels
                          Rogier Koning

                          This is the excel file of my personal datapoint of symptoms and some treatments.


                          I'll add also my complete diary from nov until last month, so you get an idea what one member alone is collecting.

                          • 10. Re: How to find combinations of labels
                            Paul Wachtler

                            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

                            Screen Shot 2018-11-14 at 9.34.13 AM.png


                            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.


                            Screen Shot 2018-11-14 at 9.37.26 AM.png


                            Attaching the updated Tableau file and the updated excel file (which used your updated data) that I used to create the extract.




                            • 11. Re: How to find combinations of labels
                              Paul Wachtler

                              Hi Rogier - checking in to see if my last post worked out for you.  If so, can you mark my answer as correct?  Thanks!

                              • 12. Re: How to find combinations of labels
                                Rogier Koning

                                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?

                                • 13. Re: How to find combinations of labels
                                  Paul Wachtler

                                  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.