13 Replies Latest reply on Jun 3, 2012 2:46 AM by Jonathan Drummey

    filtering calculation

    Luis Amadeo

      I am not sure how to approach this; it may be a calculated filed or a filtering formula. I have data that tracks incidents over a period of time for patients in a hospital. I need to produce a report on a daily/weekly/monthly on patients who have had 2 or more incidents on a 24 hr. period. It doesn't matter what period but two or more incidents in any 24 hr. period would qualify. I have fields for patient, date/time of incident, type of incident, etc. Not sure how to approach this. A parameter? A calc. field? Combination?  Thanks in advance.

       

      Luis

        • 1. Re: filtering calculation
          Jonathan Drummey

          Hi Luis,

           

          I work a lot with readmissions data so I'm familiar with this type of problem. It's one that's easier to solve with queries in the underlying data source rather than in Tableau, and once you get the query done then the views in Tableau are simple to create and performance is improved in Tableau.

           

          I've attached a workbook that has a query implemented as Custom SQL, using the data in the attached Excel file. Here's the query:

           

          SELECT [Data$].[Date] AS [Date],

            [Data$].[Incident] AS [Incident],

            [Data$].[MRN] AS [MRN],

            [Prior].[Prior Incident Date] AS [Prior Incident Date],

            [Prior].[Prior Incident] AS [Prior Incident]

          FROM [Data$]

          LEFT JOIN

          (SELECT [Orig].[Incident] AS [Prior Incident],

            [Orig].[Date] AS [Prior Incident Date],

            [Data$1].[Incident] AS [Incident]

          FROM [Data$] [Orig]

            INNER JOIN [Data$] [Data$1] ON [Orig].[MRN] = [Data$1].[MRN]

            WHERE [Orig].[Incident] <> [Data$1].[Incident]

             AND ([Data$1].[Date] >= [Orig].[Date] AND [Data$1].[Date] < DATEADD('d',1,[Orig].[Date]))) AS [Prior]

          ON [Data$].[Incident] = [Prior].[Incident]

           

          We start out getting all the incidents, and then left join that to a subquery (the second SELECT) that makes up a list of all incidents that have another incident that is within 1 day prior. It's a little messy because there are effectively two self-joins, but it gets the job done. Also, if there was more than one prior incident within the 24 hours, then multiple rows are returned - you can see this in incident K on the Custom SQL Data worksheet.

           

          Now we have a set of results that has each incident and whether there was a prior incident within 24 hours, and what incident that was.

           

          From here, you can do most anything you want. You can show the patients, count incidents, do a count distinct of MRNs (Medical Record Numbers), etc. If you're using Access, Excel, or text files as a datasource then you will need to use an extract to get access to the count distinct function.

           

          To filter by day/week/month and show results over time, the approach here works quite well:

           

          https://www.interworks.com/blogs/iwbiteam/2012/05/21/creating-dynamic-anchor-date-relative-date-filter

           

          Let me know if this works for you!

           

          Jonathan

          • 2. Re: filtering calculation
            Luis Amadeo

            Johnathan, I appreciate the time you have taken to answer my post. I will give it a try against my data. It makes sense to do it this way rather than mess around with parameters and filtering fields. Thanks, I'll let you know how it worked.

             

            Luis 

            • 3. Re: filtering calculation
              Luis Amadeo

              Jonathan, I reviewed your Custom SQL and file and found that I failed to explain that the data I have is for all incidents. Your response assumed that I had a database of all patients in the hospital and then a data base of incidents. What I have is a database of all incidents. I did manage to filter out those patients with 2 or more incidents in one day using a If statement. However, that falls short as I need two or more incidents in any 24 hour period. So, I think I need to construct a statement that checks the time between each incident for each patient then counts the number of incidents from that first incident and if there are two or more then flag the patient. I hope this is clear. I think I have the logic but not the skill to construct it in Tableau.

               

              Again, thanks for your help.

               

              Luis

              • 4. Re: filtering calculation
                Jonathan Drummey

                Hi Luis,

                 

                The dataset I created is just a table of incidents with 1 to 4 incidents per MRN (patient), and only includes the MRN, Incident, and Incident Date. I'm not sure how you were thinking I was assuming there was a separate dataset of patients?

                 

                To get an exact 24 hour time period, all you need to do is change the DATEADD() calc in the Custom SQL from:

                 

                DATEADD('d',1,[Orig].[Date])

                 

                to this:

                 

                DATEADD('h',24,[Orig].[Date])

                 

                I've updated the attached workbook to reflect this.

                 

                As for the output, in your original post you had specified needing to report on patients who had 2 or more incidents over a 24 hour time period, the "Output 2 - Just MRN" gets those patients. If you want to report on the 1st, 2nd, or Nth incident that occured in that timeframe along with the patient info, I'm sure a calculation could be developed that would meet your needs, however I'd need a better idea of what your desired output is.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: filtering calculation
                  Luis Amadeo

                  Sorry, you are correct. I thought that the list was of patients and patients with incidents but I see that you have incident A, incident B. So it's all incidents and not two different data sets. Thanks again,

                   

                  Luis Amadeo

                  • 6. Re: filtering calculation
                    Luis Amadeo

                    Johnathan, I got the custom sql but it's giving me the following error message:

                     

                    Microsoft JET database error 0x80040E10: No value given for one or more required parameters.

                     

                    Unable to connect to the Microsoft Excel file "C:\Documents and Settings\lamadeo.EHS\My Documents\qryRestraints Sept 2001.xls". Check that you have access privileges for the requested file and that it is not open in another application.

                     

                    This is the sql I modified for my data:

                     

                    SELECT ['qryRestraints Sept 2001$'].[EpisodeStarttime] AS [EpisodeStarttime],

                      ['qryRestraints Sept 2001$'].[Type] AS [Type],

                      ['qryRestraints Sept 2001$'].[ClientID] AS [ClientID],

                      [Prior].[Prior Incident Date] AS [Prior Incident Date],

                      [Prior].[Prior Incident] AS [Prior Incident]

                    FROM ['qryRestraints Sept 2001$']

                    LEFT JOIN

                    (SELECT [Orig].[Type] AS [Prior Incident],

                      [Orig].[EpisodeStarttime] AS [Prior Incident Date],

                      ['Restraints Sept 2001$1'].[Incident] AS [Incident]

                    FROM ['qryRestraints Sept 2001$'] [Orig]

                      INNER JOIN ['qryRestraints Sept 2001$'] ['Restraints Sept 2001$1'] ON [Orig].[ClientID] = ['Restraints Sept 2001$1'].[ClientID]

                      WHERE [Orig].[Type] <> ['Restraints Sept 2001$1'].[Type]

                       AND (['Restraints Sept 2001$1'].[EpisodeStarttime] >= [Orig].[EpisodeStarttime] AND ['Restraints Sept 2001$1'].[EpisodeStarttime] < DATEADD('h',24,[Orig].[EpisodeStarttime]))) AS [Prior]

                    ON ['qryRestraints Sept 2001$'].[Type] = [Prior].[Type]

                     

                    Any thoughts?  thanks,

                     

                    Luis Amadeo

                    • 7. Re: filtering calculation
                      Jonathan Drummey

                      Hi Luis,

                       

                      From walking through the code I can't see any specific issue. That error typically shows up when you have a field defined in the query that doesn't exist in the underlying data, for example if a fieldname was "ABC" and you had mistyped [ABD]. What I do to try to debug situations like this is to break down the query into its parts. So, for example, try just this portion of the query:

                       

                      SELECT [Orig].[Type] AS [Prior Incident],

                        [Orig].[EpisodeStarttime] AS [Prior Incident Date],

                        ['Restraints Sept 2001$1'].[Incident] AS [Incident]

                      FROM ['qryRestraints Sept 2001$'] [Orig]

                        INNER JOIN ['qryRestraints Sept 2001$'] ['Restraints Sept 2001$1'] ON [Orig].[ClientID] = ['Restraints Sept 2001$1'].[ClientID]

                        WHERE [Orig].[Type] <> ['Restraints Sept 2001$1'].[Type]

                         AND (['Restraints Sept 2001$1'].[EpisodeStarttime] >= [Orig].[EpisodeStarttime] AND ['Restraints Sept 2001$1'].[EpisodeStarttime] < DATEADD('h',24,[Orig].[EpisodeStarttime]))

                       

                      and see if you get the error. That will narrow it down to this part, at least, and then you can do things like run simple SELECT queries on each field used in the query's SELECT and WHERE clauses to make sure they are accessible.

                       

                      Good luck!

                       

                      Jonathan

                      1 of 1 people found this helpful
                      • 8. Re: filtering calculation
                        Luis Amadeo

                        Yes, I found the error and it worked. I am looking at the data now, but it looks ok at first glance. Thanks for your help. I'll let you know if there are any other issues.

                         

                        Luis Amadeo

                        • 9. Re: filtering calculation
                          Jonathan Drummey

                          You're welcome!

                          • 10. Re: filtering calculation
                            Luis Amadeo

                            Yes, it's displaying the correct patients who have had more than one incident over any 24 hour period. That's great! Thanks for your help; it has given me a great insight in developing custom sql. One more question. What would be the best way of showing the date/time of the last incident along with the date/time for the next prior incident?

                             

                            Luis Amadeo

                            • 11. Re: filtering calculation
                              Jonathan Drummey

                              What's your goal? If you're trying to communicate exact times, then a simple text table showing the times would be what I'd do, with maybe a horizontal bar chart that shows the time between incidents. If you're trying to communicate something about totals, you might use a text table or a bar chart. Beyond that, if you're trying to communicate something about the time of day or duration, you might use a bar chart, a circle chart, or a Gantt bar chart, and when getting into time series (how many events occured on a given date), line charts and bar charts are useful. The exact choice, though, really depends on the story you are trying to tell.

                               

                              Jonathan

                              1 of 1 people found this helpful
                              • 12. Re: filtering calculation
                                Luis Amadeo

                                Thanks, any of those options would be helpful. I am just having trouble with the dates not showing up as date/time. I can get the last episode to show date/time but then the prior episode doesn't. Not sure why. I'll play with it and see I can fix it. Thanks,

                                 

                                Luis Amadeo

                                • 13. Re: filtering calculation
                                  Jonathan Drummey

                                  Hi Luis,

                                   

                                  When you see the field in the Dimensions window does it show up with the calendar icon or the calendar & clock icon? If the former, then somehow Tableau is interpreting it as a Date field and not a DateTime field. Another thing is to check the Format of the field in the view, it could be formatted to just display the date.

                                   

                                  Jonathan