5 Replies Latest reply on Jun 14, 2016 9:25 AM by Tom W

    Tracking visit compliance - one date counts twice

    Nicole Le Prohn

      I need to show monthly caseworker visit compliance. Unfortunately it isn't as easy as seeing if there is a visit recorded each month. "Monthly" means every 35 days or less. A single visit can 'count' as compliant for two months. My customer wants to look at a graph with rolling 12 month compliance as of the 15th of each month. So, if  client A has visits on 1/14 and 2/28 - my customer wants this to count for 3 monthly contacts (1/15, 2/15, 3/15) since the visit on 1/14 was within 35 days of 2/15. My data source has one row per visit per client -so Client A has 2 rows of data.

       

      I've been working on this for hours and cannot wrap my head around how to either pull the data differently (using SQL) or blend a duplicate data source, or come up with a calculations....

       

      Any help much appreciated.

        • 1. Re: Tracking visit compliance - one date counts twice
          Tom W

          Hi Nicole,

          There really isn't enough to go off of here. What's your actual question? What's your stumbling block? What's the structure of your data?

           

          It would help a lot if you could attach a Tableau Packaged Workbook or a sample with the same data structure.

          • 2. Re: Tracking visit compliance - one date counts twice
            Nicole Le Prohn

            Here's a sample workbook. On the dashboard I show a sample record with visit dates. The First visit shown needs to count twice.

            • 3. Re: Tracking visit compliance - one date counts twice
              Tom W

              It's not really clear to me based on the first example or the attached example what your business logic is for each period. You've stated 35 days but I don't understand it's that 35 days before the 15th, 35 days after the 15th or either side.

               

              I do think you're going to need to do this in SQL as you're effectively going to have to 'fake' records in the months where they don't exist. I'd imagine some type of a cross join or union, but it's hard to say without truly understanding your windows of time.

              • 4. Re: Tracking visit compliance - one date counts twice
                Nicole Le Prohn

                Tom: Sorry if I wasn’t clear. It is 35 days before the 15th. For example, on June 15 we look back 35 days and count the number of clients who had at least one visit.

                I agree, I think I need to do something as part of the SQL query, I just can’t figure it out. I have sort of figured out a clumsy work-around using the makedate function to check if visits fall into the 35 day window each month. I’ll post that when I finish it.

                 

                 

                 

                Niki Le Prohn

                Director-Data Driven Practice

                Data Advocacy

                P 206.270.4925

                C 206.854.5093

                F 866.282.3797

                nleprohn@casey.org<mailto:NLeProhn@casey.org>

                Building on 50 Years of Investing in Hope  |  1966-2016

                • 5. Re: Tracking visit compliance - one date counts twice
                  Tom W

                  I'm going to preface this with my usual 'there's many ways to skin a cat', but I see one hurdle with this problem which will be periods where 0 visits took place. Thus, we need to 'fake' the data in order for say June 2016 to show if no visits took place in the 35 days before June 15.

                   

                  I normally accomplish this type of a thing in SQL by using Cross Joins. I've prepared an example here with a helper table which effectively contains a list of dates on the 15th for each month in 2016. There's a bunch of ways you can generate this table, you could save it as a Dimension table in your database for permanent re-use, or create it as a table function, or even just nest the sql within a SELECT statement without saving the object at all. The right answer will depend on your situation.

                   

                  Then I created a fake visits table which includes a client, employee name and Visit Date. My sample data;

                  VisitDateEmployeeClient
                  2016-01-10JohnACME Solutions
                  2016-01-20PeterACME Solutions
                  2016-03-01SallyACME Solutions
                  2016-01-14JohnWombat Inc
                  2016-02-14PeterWombat Inc
                  2016-03-14SallyWombat Inc
                  2016-04-14SallyWombat Inc

                   

                  The magic is in the final select statement where I start by selecting the dates from the ReportingDates table (the list of the 15th dates),cross join it to a distinct list of clients then left join it to the visits where the Visit Date is between the Reporting Date and 35 days earlier.

                  SELECT
                    ReportingDate,
                    Client,
                    COUNT(VisitDate) VisitCount
                  FROM (
                    SELECT d.ReportingDate, c.Client, v.VisitDate, v.Employee
                    FROM #ReportingDates d
                    CROSS JOIN (SELECT DISTINCT Client FROM #visits) c
                    LEFT JOIN #visits v on c.Client = v.Client and v.VisitDate between dateadd(d,-35,d.ReportingDate) and d.ReportingDate
                    ) a
                  GROUP BY
                    a.ReportingDate, 
                    a.Client
                  

                   

                  And my output in Tableau looks like;

                   

                  I've attached some sample SQL utilizing temporary tables so you can replicate exactly what I've shown.

                  2 of 2 people found this helpful