It can be done with Tableau. The definitive thread on this type of solution is here: http://community.tableau.com/message/198288#198288
However, it is a rather complex solution and if there is any way to shape the data so it looks like the table in your desired outcome, it will be quite a bit easier to work with in Tableau. What is your initial data source?
If after going through the thread above you have any questions, I'd be happy to help!
I worked on this a bit this morning because I was pretty sure it could be done with Joe's 2-record scaffolding structure and a data blend. Turns out my assumption was correct, but I didn't get it completely worked out without doing a Hang Out with Joe Mako. I missed the bit about duplicating the data source and then blending one on the start dates and the other on the end dates.
Here's the result you were looking for:
Here's a step-by-step description of how Joe got there:
1. Create an Excel worksheet that looks like this:
2. Connect to this worksheet
3. Create a Start Date parameter with current value set to 1/1/2013
4. Create an End Date parameter with a current value set to 1/20/2013
5. Create a calculated field named Date:
CASE [Record] WHEN 1 THEN [Start Date] WHEN 2 THEN [End Date] END
[Or you could do it as an IF/Then or IIF for that matter]
6. Put this Date calc on the row shelf as a discrete date.
7. Right-click and check the "Show Missing Values":
Because of Data Densification we've turned 2 records/rows into 20:
Now that we've built the scaffolding, we're ready to populate it with data.
8. Duplicate the data source, renaming one of them In-Flight Starts and the other In-Flight Ends (or something similar)
9. Go up to Data\Edit Relationships and set up custom relationships so that the Date in 2-Record Scaffold is linked to Start Time in the In-Flight Starts data connection.
10. Do the same for the In-Flight Ends data connection. (We'll have to edit this later, but normally we won't.)
11. Staying in the 2-Record Scaffold (primary) data source, create a calculated field named Starts containing this:
SUM([In Flight Data Starts].[Number of Records])
12. Now create another one named Ends containing this:
SUM([In Flight Data Ends].[Number of Records])
13. Now we need to calculate the # of Events that are open on each date. Create a table calculation containing this:
Set the Default Table Calculation to compute using Date. Also pay attention to the ZN() function, otherwise the calc won't fully populate the scaffold. Drop this on the text shelf and you should end up with this:
We are close but it isn't the exact figures you were looking for. Your expected results were shifted down one row. You wanted to include the end date in the count.
14. To fix this Joe went to the In-Flight Data Ends data connection and created a calculated field named Shifted End containing this:
One of the reasons I love watching Joe use Tableau is I learn little stuff along the way. For instance why did he wrap the DATEADD() function in a DATE() function? [Answer at the bottom of the page.]
15. Now you'll need to go back up to the Data\Edit Relationships and edit the In-Flight Data Ends, changing it from Date = End Time to Date = Shifted End:
That's it, now your numbers will be as requested.
16. Duplicate the sheet and turn it into the line chart you wanted.
John, thanks for presenting us with such a well-asked question. You showed your problem, gave us very specific examples of the expected outcome and included a packaged workbook. We all wish everyone asked such well formed questions. It was a pleasure working it out (well watching Joe work it out). Thank you.
Answer: DATEADD() returns a Date-Time date type that you then need to manually change to a Date date type. DATE() returns a Date date type that doesn't need to be changed. The other little short-cut I learned watching him work last year is that if you highlight the entire line of code and then double-click the DATE() function in the list T will wrap the whole line in the DATE() function correctly (NOT delete the whole line and replace it with the function, as most Windows programs would do).
Someone should do a blog about all the little time-saver techniques.
Anyway, hope you found this helpful and educational, I know I did -- thanks Joe!
In-Flight-JM.twbx.zip 24.7 KB
Thanks for the nice example of scaffolding. When I was going through it I noticed that I had to change the data type of the Start Time / End Time in the original data source(s) to Date rather than Datetime for the blend to work. For anyone else not seeing the expect result after step 13, this might be the reason. ...
Wow, thank you so much to all who responded. I've never seen so many people jump in to give such a comprehensive answer to a question. Now all I have to do is decide between my multitude of options.