2 Replies Latest reply on Jul 28, 2016 10:44 AM by david.huffer

    Counting entries and exits per dimension over time

    david.huffer

      All,

       

      We are just beginning to work with Tableau at my agency so please excuse my ignorance here. 

       

      I have a couple of seemingly simple problems that I could use some help on. 

       

      I have a sample dataset attached with IDs and start and end dates representing when an ID started a program and when that ID ended a program.  There are various program types and subtypes but I'm ignoring the subtype here for simplicity. I'm also ignoring the stock population of IDs that are in each program at the start of the data collection. I'm assuming all programs have zero IDs at the outset.

       

      It's easy enough to count the number of times within each month-year an ID started a program and the same for when an ID ended a program for each program type.  And calculating the average program duration for each is easy as well.  Those are shown in the first 3 worksheets in the attached.  These are all simply text tables.

       

      What I would like to do is to describe a running sum of IDs per program, that is, take the number of IDs in the program at the start of each month-year (or week number within) plus the number of entries during that period minus the number of exits during that period. 

       

      So, in the attached sample, say Type A began 2015 with zero IDs and then there were 8 new entries and 8 exits over time.  I want to calculate the last column in the following table and use it elsewhere.  Like in a waterfall plot:

       

                  +  -

      2015    Jan 0  0 = 0

      2015    Feb 0  0 = 0

      2015    Mar 1  0 = 1

      2015    Apr 1  0 = 2

      2015    May 3  2 = 3

      2015    Jun 0  1 = 2

      2015    Jul 0  2 = 0

      2015    Aug 0  0 = 0

      2015    Sep 1  1 = 0

      2015    Oct 0  0 = 0

      2015    Nov 0  0 = 0

      2015    Dec 2  1 = 1

      2016    Jan 0  0 = 1

      2016    Feb 0  1 = 0

       

      I'm just not exactly sure how to perform that calculation and then re-use the results.