3 Replies Latest reply on Oct 4, 2017 3:28 PM by Kara Follmer

Historical Data, Total Open Projects at Any One Time

Howdy Folks,

For the purposes of this I have data With 3 columns

ProblemID OpenTime ClosedTime

Many of these are open for a few months or so at a time, I already have this in a Gannt Chart for visual purposes, but what I'd like to do is get these where I can view them on a line graph, daily or weekly. I'm still unsure which to use.

I would use the DATETRUNC() Function in order to move all the hours, minutes, and seconds to just the beginning of each day (or week). If it was open at one point in that time period I would count it, would be my reasoning.

I'm looking for hints how my formula would work to make that line graph show at any given interval (day or week), the total number of open projects there were at that time.

I've done this with a parameter, but it only returns one value at any specific time, here's what I did.

IF [TimeParameter] < [OPEN_TIME] OR [TimeParameter2] >[CLOSE_TIME] THEN 0

ELSEIF [OPEN_TIME] < [TimeParameter] AND [CLOSE_TIME] > [TimeParameter2] THEN 1

ELSEIF IIF(ISNULL([CLOSE_TIME]), 1, 0) = 1 THEN 1

ELSE 0 END

This literally says if the time chosen by the analyst is less than the row's open time or its greater than the close time to not count it

If the time is greater than open and less than close to count it

Or if the project is still not closed to count it.

This works perfectly but it only returns the one value for that specified time, I am unsure how to make this an actual function that can be graphed.

Thank you,

Carl

• 1. Re: Historical Data, Total Open Projects at Any One Time

A line graph of what exactly?

Can you attach a Tableau Packaged Workbook with some sample data?

• 2. Re: Historical Data, Total Open Projects at Any One Time

I have included a workbook. In this workbook I don't have any formulas or the sort. I had to use Excel's random generator as sending even real dates would break our proprietary and confidential internal regulations here.

The line graph would depict volume at any given time, the volume would be the problems that are currently open. That are not closed at that point in time.

• 3. Re: Historical Data, Total Open Projects at Any One Time

The FAQ: Open & Close Date link above references Showing Records That Fall Within a Period of Time | Tableau Software which should get you what you need after a small modification to change into a line graph at the end. In the same thread, I posted a link to a workbook where I was able to apply this successfully.