1 of 1 people found this helpful
Not sure where you are in the process here, but whenever I have to create a report around "balances" (think beginning balance, opened, closed, ending balance), I structure the data in a specific way.
This may or may not help, but I think it's one way to accomplish what you're after.
Start by making a table which has just a single column--the time interval you're interested in.
Sounds like for you, it'd be months. We often use days (with no timestamp data).
...and so on
Then, in Tableau, connect to a custom SQL (I'm familiar with SQL Server) data source which pulls that table with a LEFT JOIN to all your results records. The idea here is that you want to evaluate every record for every possible "time interval" to determine whether the record was open or closed during that time--here are cases statement to do this:
(## is the time interval of your Dates--mm if you want to evaluate a full month.)
CASE WHEN [StartTime] >= [Date] OR [EndTime] < DATEADD(##,1,[Date]) THEN 0 ELSE 1 END AS BeginningBalance
CASE WHEN [StartTime] BETWEEN [Date] AND DATEADD(##,1,[Date]) THEN 1 ELSE 0 AS Opened
CASE WHEN ISNULL([EndDate],'12/31/9999') BETWEEN [Date] AND DATEADD(##,1,[Date]) THEN 1 ELSE 0 AS Closed
CASE WHEN [StartTime] < [Date] OR [StartTime] BETWEEN [Date] AND DATEADD(##,1,[Date])
AND ISNULL([EndDate],'12/31/9999') >= DATEADD(##,1,[Date]) THEN 1 ELSE 0 END AS EndingBalance
Hope this is useful...
I believe this has pointed me in the right direction, but I still can't get the data to show up correctly. A little background:
-All the data is from an Oracle database. I'm not sure if this has an effect on how to resolve the issue, but felt it important to mention.
-I'm quite new to Tableau, so I'm still familiarizing myself with all the functions available. On that note, I'm not sure how exactly to join a custom table of dates and time intervals with the Oracle data.
Once again, any help is appreciated. Thanks.
Would you mind posting a packaged workbook (.twbx file)? It would be helpful to know how the workbook is currently built.