6 Replies Latest reply on Apr 10, 2018 10:19 AM by Jim Dehner

    Filter Data in table with multiple conditions

    Suzanne Kern

      I have attached excel file with abbreviated data and sample Tableau report.

      Attached data is not real data , generic names and scores for privacy resons.

       

      In short:

      • have a list of patients for a time period (time period is filtered in the report)
      • there are two consult types (initial and followup)
      • Drug name is another field (table will summarize by drug)
      • patient receives a "Score" on initial and on followup
      • want to filter for patients that ONLY have both initial AND followup consults (by drug) in the time period
      • if there are two dates for initial take latest date and if two dates for followup take latest date
      • chart to show down the left drug name and summarize ALL patients that have initial AND followup the score for each consult type
      • consult type (initial or followup up) will be across to top (columns)
        • 1. Re: Filter Data in table with multiple conditions
          Jim Dehner

          Hi

          Please see the attached approach

          first find the max date for the criteria

          then the score on that date

          then the filter for initial/follow up

           

          and it returns this

           

          and in summary

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Filter Data in table with multiple conditions
            Suzanne Kern

            This is fantastic!!! Thank you for the quick answer. It does exactly what I needed.

            One small issue in last step. I see how the "both initial and follow-up" filter SHOULD work in your example, but for some reason in my file with all data it is not working.

            For some reason everything coming up as TRUE. Off the top of your head, do you know why this may happen?

            In the meantime I'll try and duplicate in a smaller report, again withsensitive data taken out, so you can see what is happening on my end.

             

            Again, many many thanks!

             

            Suzy

            • 3. Re: Filter Data in table with multiple conditions
              Jim Dehner

              took a quick look and the filter here is an inequality which checks to see that the min consult type (follow up) is not equal to the Max initial - it is doing a literal comparison -   so that would be true only it the patient had both

               

              { FIXED [Patient Name]:max([Consult Type])<>min([Consult Type])   }

               

              this determines the date by consult type and that date is used to get the score

               

              { FIXED [Drug Name],[Patient Name],[Consult Type]:max([Consult Date]) }

               

               

              I would check by taking a patient that should not meet the filter expectation and look at the actual data and the values of consult type and max dates

               

              Let me know

              Jim

              • 4. Re: Filter Data in table with multiple conditions
                Jim Dehner

                Good morning

                I realized late last night in your real data teh filter would need to include the drug name-

                it should look like this              

                                              { FIXED [Patient Name],[Drug Name]:max([Consult Type])<>min([Consult Type])   }

                 

                Jim

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                • 5. Re: Filter Data in table with multiple conditions
                  Suzanne Kern

                  Thank you again so much for your help!

                  I am still having trouble getting the last filter (both initial and follow-up )to work in my final report.

                  It works for the example I gave (so your answer is correct), just something extra in my full report is messing with it.

                  I just need to figure out how to replicate my report in a file suitable to post here.

                   

                  Suzy

                  • 6. Re: Filter Data in table with multiple conditions
                    Jim Dehner

                    Thanks

                     

                    Know you can't share the book (I did some work for someone in the industry - the nda was incredible) but if you can send me a picture of the dimensions you have in the viz I will look at it - also you could send it to me at marketanalyticsllc@gmail.com to avoid posting it

                     

                    Jim