2 Replies Latest reply on Mar 2, 2017 10:08 AM by Sarah McGraw

    How to count clients served each quarter when you have a service date range

    Lindsay Eivazian

      Hello community,

       

      I am a new Tableau user.

       

      I am trying to display client data by quarter. However, I don't have discrete service dates, I have service date ranges (e.g. each client has a start and end date--or no end date if the client is still being served)

       

       

      I am trying to display quarter data beginning 10/1/14-9/30-15 (federal quarters).

       

      Q1 - 10/1/14 - 12/31/2014

      Q2 - 1/1/2015 - 3/31/2015

      Q3 - 4/1/2015 - 6/30/2015

      Q4 - 7/1/2015 - 9/30/2015

       

      Some clients entered 10/1/2003, some entered 2/2/2915, it varies because what this data set shows is all clients served or active between 10/1/14 and 9/30/2015. The exit dates fall within that date range or are null because the client is still active.

       

      I am trying to display the data like the attached image, by project type and by federal quarter and year (2014 Q1, etc).

       

      If the client entered 10/1/14 and stayed throughout the whole year, I want that client counted in all four quarters.

       

      If the client entered in 2003 and exited 2/2/2015, I want that client counted in two quarters (2014 Q1 and 2015 Q2).

       

      I'm also having a hard time dealing with the null values, where the client isn't exited yet, in that case the client would need to be counted in all four quarters but still shouldn't have a null displayed.

       

      My issue is very similar to the thread here but not quite because the solution posted was how to get the client to be counted in the quarter he entered (counted 1 time in 1 quarter). Healthcare - how to filter/calculate by date range

        • 1. Re: How to count clients served each quarter when you have a service date range
          Steve Mayer

          Lindsay -

          Attached is a packaged workbook (Tableau 9.0) that solves the problem with a slight modification to your data source. I have attached the spreadsheet so you can see how it looks in Excel as well. The solution isn't simple, but it looks like it gets you where you want to be & affords the opportunity for a ton of Tableau learning... so here we go:

           

          Step 1:

          Add a new sheet to your Excel sheet with two columns - a "key" (which will always be 1) and the quarter (I included the 4 quarters you mentioned):

            

          DateKey
          10/1/20141
          1/1/20151
          4/1/20151
          7/1/20151

           

          Step 2:

          Add a new column to your existing Excel sheet called "key", which will always have a value of 1

           

          Step 3:

          Modify your data source in Tableau to join your existing sheet to the new sheet. You are going to do a left join on the "key" column.

          This will create 4 times as many rows in your data source - you are going to now have 4 rows per original row, one for each quarter in the new Excel sheet.

           

          Step 4:

          Extract the Excel data as a Tableau extract (Go to the Data menu... Your Data Source... Extract Data... and click OK). This will have better performance over using the underlying Excel data source.

           

          Step 5:

          Create a calculated field that returns the Exit Date for any client. Because Exit Date can be NULL and then change to an actual date, you'll want to use an LOD calculation to return the non-null Exit Date for any of the 4 quarters (there will still be NULL values for clients with no Exit Date. The formula is:

           

          { FIXED [Client Unique Id] : MIN([Entry Exit Exit Date]) }

           

          Step 6:

          Create a calculated field that returns TRUE if the client was in fact a client in the Quarter from the new Excel sheet:

           

          DATETRUNC('quarter', [Entry Exit Entry Date]) <= [Date]

          AND

          (ISNULL([Exit Date]) OR DATETRUNC('quarter', [Exit Date]) >= [Date])

           

          Step 7:

          Test your calculated field. I created a cross-tab that shows each client by quarter and their status in that quarter:

           

          Step 8:

          Create a calculated field that counts distinct clients where they were a client.

          COUNTD(IF [Is Client] THEN [Client Unique Id] END)

           

          Step 9:

          Show it all in a Viz by Quarter:

          -Steve

          2 of 2 people found this helpful
          • 2. Re: How to count clients served each quarter when you have a service date range
            Sarah McGraw

            Wow, I have been having a very, very similar problem, and I ended up running a SQL script to do this datemath for me for the last project. However, I have to do this kind of thing all the time, so I will study your solution. For now, do you have any opinion about whether it is better to do these kinds of duration service/ membership date calculations in Tableau vs in the source data?