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

# Counting entries and exits per dimension over time

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.

• ###### 1. Re: Counting entries and exits per dimension over time

David,

Welcome to the Forums.

I think your desired goal can benefit from the ideas on this excellent link:

CROSS JOIN with Tableau's join dialog

(kettan, this shows up on my browser as a highly visited site. Thank you again for it.)

If this all works well with your setup, you can add a Key column of all 1s to your data worksheet,

and then the make a new sheet of the same Key column and then a column of all the possible dates.

This creates the combination of all possible dates, but it can be filtered using:

[Date]>=[Start Date]

AND [Date]<=[Discharge Date]

From here, I returned the Start IDs and The DC IDs:

IF [Date]=[Start Date] THEN [ID] END

Similarly

IF [Date]=[Discharge Date] THEN [ID] END

You can also CountD these:

COUNTD(IF [Date]=[Start Date] THEN [ID] END)

Then you do a Running Total on the Delta:

[CountStart] - [CountDCs]

This Delta calculation should be amenable for graphing.

There are some nulls, I wasn't sure you needed them padded to zeros.

• ###### 2. Re: Counting entries and exits per dimension over time

Thank you so much. Your response was incredibly enlightening.