It would be very helpful if you would attach a packaged workbook so the community can get a better idea of the structure of your data and validate any proposed solutions. See this article: Why we ask for a workbook, and WHY I ASK FOR YOUR TABLEAU VERSION!!
Without seeing your data, I can only speak generally. Anytime you have a scenario where you essentially need to account for a row twice (once when it appears, and again when it drops off), it's usually a sure sign that you need to pivot your data. So instead of:
Unique Key Start Date End Date Metric 12345 2016-01-01 2016-03-15 42
After pivoting, it will look like:
Unique Key Date Event Metric 12345 2016-01-01 Start Date 42 12345 2016-03-15 End Date 42
This allows you to maintain a running average of the number of active cases, by triggering a +1 or -1 change in response to each Event in your pivoted data. The running average, then, can be incorporated into your moving average to yield the report you want.
Tableau can automatically pivot data from file sources such as Excel and CSV. If your data comes from a DBMS, you can write a Custom Query to accomplish the same thing. If you are using a data source that does not support Pivots, Unions, or Custom Queries, then you may need to prep your data outside of Tableau, manually or using an ETL tool.
I hope this helps. If you need any additional detail, I will ask that you attach a packaged workbook for review. Thanks.
thank so much for spending time helping me out. I set up the data the way you explained. What can I do If I now want to to exclude the end dated metric for any calculation taking place after 2016-03-15? Would I create a calculated field?
On a related note, is the moving average cumulative in Tableau? My calculations do not seem to be including the records from previous quarters because the number of participants do not increase as my line graph progresses. I actually get smaller numbers of unique participants at various points (numbers displayed below). This number discrepancy would not be do to participants falling off.
Excuse me for not sharing a workbook.
Yes, if you want to exclude the end date metric, you should be able to do so in your calculated field.
You should already have created a calculated field to generate the +1 and -1 changes to use for your running total. For instance:
IF [Event] = 'Start Date' THEN 1 ELSEIF [Event] = 'End Date' THEN -1 END
You can simply modify that to check the date before generating a -1.
IF [Event] = 'Start Date' THEN 1 ELSEIF [Event] = 'End Date' AND [Date] <= #2016-03-15# THEN -1 END
Regarding your other question: a moving average is not cumulative in Tableau. It is simply an average of the last N data points. Depending on what you want to do, the running total of the above calculated field will probably be sufficient. (You said moving average so I thought maybe you were taking a moving average of the running total, which is a different concept.)