I have a list of services for which I need to report on monthly outages & availability. To calculate the availability I need to know the number of seconds per day that the service should be available for and the total outage time on that service. My report is based on two tables, joined via a left outer join – a list of all services and a list of all outages on those services, joined by the “Service ID” - see attached diagram.
I need to produce tables which show, for example, service availability per “region” per month or service availability per “medium” per month.
I’ve run into 2 problems:
- If a particular service does not have any outages in a given month the resulting data set contains no row for that service ID / month, meaning the “SecondsPerDay” used in any calculated field with a filter applied for that month is “NULL”. However, I still need to include the “SecondsPerDay” for that “ServiceID” when calculating availability per region, for example.
- When calculating the total “SecondsPerDay” per “Region” or “Medium”, there is risk of counting the “SecondsPerDay” for a given service multiple times, if it has had multiple outages.
I'd be grateful for any advice on if there is a better approach than the left outer join here, or anyway of working around the issues above
Hi, @Nigel Smith
I don't know if it's what you want, but please check it if you like.
One idea. I don't know how much data it is, but for example, is it possible to have calendar information in the service list? (Prepare the required period of year and month information for each service ID)
If this is available as master data, it may be possible to use it by left joining.
(If possible, relationships may be easier to handle than joins because they are aggregated on the Tableau side and then linked)
I don't know if it will be helpful, but I hope it will be of some help to you.
*If you get the best results from this exchange, I would appreciate it if you could choose the best answer or upvote.
