I have two tables I'm working with.
A system table and an outage table.
They both list System, Site, and start and stop dates
(system operational/decommissioned dates and outage start/end dates).
My end result,
I want to get the duration of outages (outage end - outage start) from the outage table and divide that by the total count of active systems (count system) from the system table to see the total downtime across all systems by month(calendar date) for this dashboard I'm building with start and end parameters for the client to see system downtime from month to month or week by week.
I learned I should do date scaffolding but doing that in Tableau Desktop doesn't help my calculations. When I try to do it in Desktop and reference the count of active systems by month in my outage table with the outage date scaffold, I get total count of systems in table across the board just something like 200, 200, 200, 200, 200. And data blending ruins everything and greys everything out or turns my calculations red. But I want to get total count of active systems each month like 190, 188, 170, 195, 192.
So I figured I need to do these calculations in Tableau Prep but I don't know how to do date scaffolding in tableau prep. When I try to do it, I'm getting 2M results and don't know how to shrink it down past this step.
Was going to join the same way I did it in Tableau Desktop with the scaffolding table.
Joining systems table on Ops Date >= Calendar Date
Calculated field: IFNULL(Decomm Date,TODAY()) <= Calendar Date
and
Joining outage table on Outage Start >= Calendar Date
Calculated field: IFNULL(Outage End,TODAY()) <= Calendar Date
Then I think I need to join the systems to outages on
System Name = System Name
Site Name = Site Name
System Calendar Date = Outage Calendar Date?
I'm just completely lost and don't know if I explained my problem correctly. But I made some fake data as an example. Don't have tableau on this computer to show you what I'm experiencing but hopefully the explanations helped. Any help would be greatly appreciated. Thank you!
Hi Dewayne,
I had some time today to work on this for you. Hopefully it points the way (conceptually) as to how to do this, and what the potential output would/could look like with the Outage dates/data combined with the System Operations dates/data. I took the same route as I did in my blog for the most part. The exception being that there wasn't anything unique in the data to key off of for purposes of partitioning the dates.
What I've found in Prep is that we can use the New Rows feature as a nice date scaffold, and it works well when there's uniqueness in the data that can be partitioned to, for example, your System ID, Site ID. That way when using New Rows feature to build out the date scaffold (don't need to introduce a separate calendar table at all), it sees the uniqueness in the rows and segments accordingly. If that partitioning wasn't there in the beginning, then New Rows simply operates off of the entire table of data (dates) and we'd get errant stop/start results. That's the background, this is the detail (I'll add separately the flow, the .hyper file, the workbook for Desktop):
1) A Union Step. I connected to the MS-Excel data you provided. Then unioned them together. NOTE: In order to properly union them together, the column/field names need to be exactly the same. So, in your System Operations dates changed those to Start and End field names and same for Outage dates changed those to Start and End. Tableau Prep will union the columns/fields together, appending them together on the common field names. It will also create a new column called Table Names.
We use that new column to build the following calculation to help delineate the differences between the unioned rows of data:
Operations/Outage:
IF CONTAINS([Table Names],'system') THEN 'System Operations Data' ELSE 'Outage Data' END
As you can see in the above screenshot it categorizes the rows of data into two dimensions. In this step, I've also addressed the null end dates for system operations dates with:
End Dates:
IFNULL([End],TODAY())
2) A Clean Step. Because there isn't anything unique in the data to key off of, the following was done to support the New Rows (Scaffold Dates). Each of the below build off each other to create a 'unique ID #':
System Row #
{ ORDERBY [System] ASC:RANK_DENSE()}Site Row #
{ PARTITION [System]: { ORDERBY [Site] ASC: RANK_DENSE()}}Unique Row ID #
STR([SystemRow#])+'-'+STR([SiteRow#])
With that we get a unique ID per site, with just System A selected, showing Site A and B:
3) A New Rows Step. We'll use the parameter settings of Start Date and the modified End Date (to grab those null end dates from the new end dates calculation). We'll name it Scaffold Dates. And the increment will be 1-day and we want to copy from previous row to fill in the detail. So the settings look like:
So this builds the 'scaffold dates' that we need in the data. In the above screenshot honed down to SYS A, we can see the outage dates are built out (scaffolded), but what you can't see is that the operations system dates are also built out as well, for this particular System of A. Each system is treated and built out differently because of the partitioning done prior.
4) A Clean Step. Simply to remove non-needed columns and simplify results.
5) An Output Step. Created a .hyper file.
6) Connected Tableau Desktop to the .hyper file and built out some LOD (Level of Detail Calc's). The below example shows for April 2012 and at the DAY-level of detail using the scaffold dates, the System Count is correct, the Site Count is correct, and the number of days outage is correct. So you could technically then use those calc's to determine % of availability to the day-level.
That's it...it should point the way. Flow attached with this post.
Best, Don
(Please, don't forget to click Select as Best or Upvote !)
