Without seeing the data it's scary to make an attempt;) but here is my "shot". I use "Scaffolding" when i have to merge two sources based e.g. date when the individual dates are not available in both sources. By adding a third source with all individual dates of the year and assigning it has the primary data source you will create a "backbone" for the other two sources against which the individual dates will match with the "backbone". Hope it make sense and is applicable in your scenario.
Good morning Norbert,
That is a very valid scenario and I really understand that. I think this is a little different. I am essentially trying to create this datasource in tableau. This is the sql query that produces the temp table with table variables. How would you create this to show the point in time status?
DECLARE @bugs TABLE(
BugID /*Defect.Defect ID*/
, DetectionDate /*Defect.Detected on Date*/
, Customer /*Defect.Customer*/
, Priority /*Defect.Priority*/
, Severity /*Defect.Severity*/
, Summary /*Defect.Summary*/
, IssueType /*Defect.Issue Type*/
, ResolutionType/*Defect.Resolution Type*/
, Status /*Defect.Status*/
, Subject /*Defect.Subject*/
, ClosingDate /*Defect.Closing Date*/
INSERT INTO @bugs(
BugID, DetectionDate, Customer, Priority, Severity, Summary, IssueType, ResolutionType, Status, StatusDate, Subject, ClosingDate, ChangeDateTime, OldValue, NewValue
- BUG.BG_BUG_ID /*Defect.Defect ID*/
, BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/
, BUG.BG_USER_09 /*Defect.Customer*/
, BUG.BG_PRIORITY /*Defect.Priority*/
, BUG.BG_SEVERITY /*Defect.Severity*/
, BUG.BG_SUMMARY /*Defect.Summary*/
, BUG.BG_USER_11 /*Defect.Issue Type*/
, BUG.BG_USER_02 /*Defect.Resolution Type*/
, BUG.BG_STATUS /*Defect.Status*/
, BUG.BG_SUBJECT /*Defect.Subject*/
, BUG.BG_CLOSING_DATE /*Defect.Closing Date*/
FROM td.BUG BUG
LEFT OUTER JOIN
AUDIT_LOG.AU_ACTION /*Audit Log.Action*/
,AUDIT_LOG.AU_DESCRIPTION /*Audit Log.Audit Description*/
,AUDIT_LOG.AU_ENTITY_ID /*Audit Log.Entity ID*/
,AUDIT_LOG.AU_ENTITY_TYPE /*Audit Log.Entity Type*/
,AUDIT_LOG.AU_TIME /*Audit Log.Time*/
,CONVERT(DATE,AUDIT_LOG.AU_TIME) AS 'STATUS_DATE'
, AUDIT_PROPERTIES.AP_OLD_VALUE /*Audit Properties.Old Value*/
, AUDIT_PROPERTIES.AP_NEW_VALUE /*Audit Properties.New Value*/
, DayRank = row_number() OVER(partition by AUDIT_LOG.AU_ENTITY_ID, CONVERT(DATE,AUDIT_LOG.AU_TIME) order by AUDIT_LOG.AU_TIME desc)
FROM td.AUDIT_LOG AUDIT_LOG /*Audit Log*/
INNER JOIN td.AUDIT_PROPERTIES AUDIT_PROPERTIES /*Audit Properties*/
ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG'
AND AUDIT_LOG.AU_ACTION = 'UPDATE'
AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG'
AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS'
ON BUG.BG_BUG_ID = StatusChange.AU_ENTITY_ID
AND DayRank = 1
AND (BUG.BG_CLOSING_DATE >= '9/30/2014' OR BUG.BG_CLOSING_DATE = '' OR BUG.BG_CLOSING_DATE IS NULL)
, @EndDate date
--IF @StartDate = '1/1/1950'
SET @StartDate = '9/30/2014'
--IF @EndDate = '12/31/2999'
SET @EndDate = CONVERT(DATE,DATEADD(DAY,-1,GETDATE()))
DECLARE @ProcessingDate date
SET @ProcessingDate = @StartDate
DECLARE @DefectStatusByDate TABLE (DefectID int, StatusDate date, DefectStatus varchar(50))
WHILE @ProcessingDate <= @EndDate
INSERT INTO @DefectStatusByDate(DefectID, StatusDate, DefectStatus)
SELECT StatusDt.BugID, @ProcessingDate, NewValue
SELECT BugID, MAX(StatusDate) AS 'RecordStatusDate'
WHERE StatusDate <= @ProcessingDate
GROUP BY BugID
INNER JOIN @bugs RDS
ON StatusDt.RecordStatusDate = RDS.StatusDate
AND StatusDt.BugID = RDS.BugID
SET @ProcessingDate = DATEADD(DAY,1,@ProcessingDate)
, CASE WHEN DefectStatus in ('New','Open') THEN 'Backlog'
WHEN DefectStatus in ('Duplicate', 'Fix Proposed', 'Fix Validated - BT', 'Fix Validated - IT', 'Fix Validated - Prod', 'Fixed', 'Fixed-Task Complete') THEN 'In Progress'
WHEN DefectStatus in ('Need Info', 'Hold') THEN 'Waiting'
WHEN DefectStatus in ('Closed') THEN 'Closed Complete'
END AS 'StatusCategory'
where statusdate >= '9/30/2014'
1 of 1 people found this helpful
I've attached a copy of a short presentation I gave at TC2012 on visualizing queue length data in Tableau that covers 3 issues that often arise. Tableau has added new features in the last 4 years, so you have a few new alternatives beyond the ones I discussed in the presentation (mostly using custom SQL)
Showing backlogs over time can be tricky because the data is not often shaped well for that purpose.
The problem you may be facing is if your data has one row per defect with two columns: one for open date, and another for close date (null for still open defects). In that case, it can be tricky to calculated queue lengths (or backlogs). One solution is to use a self union as described in the presentation. I did that with custom SQL in 2012, but you can do a union directly in Tableau now making that approach even easier. That is my preferred approach.
Marc Reuter also has shown a solution using data blending -- in that case, you duplicate your data source, rename [open date] in one source to [date], and [close date] to [date] in the other. Then blend both sources on [date]. SUM([Number of Records]) gives the number of defects opened on each day for the first source, and the number closed for each day for the second source. Then you can calculate the change in backlog each day. A risk with the data blending using dates is that only dates that appear in the primary source are queried in the secondary, so it is easy to accidentally filter out records if your data does not have at least one record per day. You can avoid this issue with a scaffolding source.
There may also be gaps in your dates when there are no data records which can lead to a distorted graphics (the first issue discussed in the presentation). Tableau demonstrated line step charts at TC2016 as a coming feature so that problem could be solved sometime soon in a 10.x release (That was the very first feature request I ever made to Tableau back in version 1.0, so I suppose you just have to be patient) Until step line charts (https://community.tableau.com/ideas/1067) are available, the typical solution is to pad the data by using a union with a "calendar" table that has one row for every date, and nulls for all fields.
Finally, take a look at the chart in the bottom right of page 2 in the attached presentation, also shown below.
This is a useful view for telling where a project is in the maturity life cycle. It shows cumulative (running total) open and closed defects in a stacked area chart. The typical progression has very few defects in the initial days because the system is not yet functional enough to test heavily, then a phase where defects are created much faster than they are being closed leading to a surge in backlog as the first draft of significant functionality comes on-line. That's a frustrating phase for managers because it seems like the defect work will continue with no end in sight, but eventually the team begins to close defects quickly as the core functionality becomes solid and they then have the ability to work off other features independently. Finally, the new defect rate begins to slow, and the backlog (orange gap) begins to narrow and you can tell you are headed towards a more stable phase. It is amazing how many projects follow this same trajectory.
Thanks for tagging me in such an illustrious list of Tableau users - all top zen masters save me. You made my day.
Hope this reply is helpful.
Thank you! I am reading your presentation now. Very nice and succinct. Your explanation here is just what I needed. I will put it together today and look like a hero. Thank you for your help. It will help me do what several others could not.
I tried to find a picture of Indiana Jones passing the remains of previous explorers, but I think you get my drift.