1 of 1 people found this helpful
I think it can be done in Tableau with judicious filtering of the joins.
I performed the joins in Tableau Prep.
I joined AlertsSheet to MaintenanceSheet on MonitorID
and then joined that result to MonitorSheet on MonitorID
I tried adding a few alerts, but wasn't sure how you wanted to handle them.
I made one alert outside of any maintenance window, and another that
barely overlapped a maintenance window. I filtered out the former using:
[Start DateTime Alert]>[End DateTime Maint]
[End DateTime Alert]<[Start DateTime Maint]
and kept the latter. But maybe you want to keep all even if completely
outside a maintenance window?
I calculated the uncovered portion on the start side using:
IF [Start DateTime Alert]>[Start DateTime Maint] THEN 0
ELSE DATEDIFF('second',[Start DateTime Alert],[Start DateTime Maint])/86400
and similarly for the uncovered end portion:
IF [End DateTime Alert]<[End DateTime Maint] THEN 0
ELSE DATEDIFF('second',[End DateTime Maint],[End DateTime Alert])/86400
This all may be completely off base.
Please see workbook v10.3, tflx, and xlsx attached in the Forum Thread:
Thanks for the effort, but I'm having trouble on the joins. In my data-set, monitors will have alerts and maintenance periods (rows) occurring at different time periods. When I join alerts to maintenance (on the monitor id) I get alerts being joined to maintenance periods that occur at different times. swaroop.gantela
I'm not sure that I caught the drift, but I think the idea would be to join all alerts to all maintenance periods
(for one monitor) and then filter down the set to just those that are of interest.
Understandably, if your tables are prohibitively large, then this join would be suboptimal for performance.
But to reduce this, further conditions could be added to the join (only those on the same day, and even
Would be grateful if you would adjust the previously attached xlsx to more closely match your scenario.