2 Replies Latest reply on Apr 15, 2018 3:17 AM by Jeevan Krishna

# Tool Availability Calculation Help

Hi,

I am working on a project in school that requires to calculate Tool Availability in Tableau and I am having some difficulties.

The formula for Tool Availability: ( 24 hours * the number of machines -  Sum(Downtime) )/ (24 hours * the number of machines)

Here is what the raw data looks like:

Production LineMachine Downtime (hours)
A12
A21
B13
B24

#1 I have tried this calculated field: ( 24*[Number of Machines] - SUM(Downtime) ) / ( 24*[Number of Machines] )

but this does not work because Tableau does not aggregate the values together for non-sum.

#2 I have also tried this: ( SUM(24*[Number of Machines] - SUM(Downtime) ) / SUM (24*[Number of Machines])

but the problem is this only sum up the number of machines that show up on the data (which makes sense because only the machine that has downtime would appear in the SQL data).

In summary, the number of machines at each production line is fixed. For example, Line A has 8 machines so the total available machine hours is 8*24 = 192 hours but if on that day, there are only 2 machines that are down and I use #2 calculation, it does not give the right number.

Production LineMachine Downtime (hours)Tool Availability
A12= (24 hours * 4 machines - (2 hours + 1 hour) )/ (24 hours * 4 machines) = 96.87%
A2196.87%
A30 downtime so will not show up in the SQL data
A40 downtime so will not show up in the SQL data
B13= (24 hours * 2 machines - (3 hours + 4 hours) )/(24 hours * 2 machines) = 85.42%
B2485.42%

Also, how should I store the number of machines in each production line (it is fixed) in the Tableau data?

Thank you!

• ###### 1. Re: Tool Availability Calculation Help

Hi Hung,

Without having all the machine's data, none of application cannot recognize total og number of machine A.

Data does not need to have down time for A3 and A4, but still need to have record of A3 and A4 like below.

 Machne Name Machine ID Down Time A 1 2 A 2 1 A 3 A 4 B 1 3 B 2 4

With above data,

Thanks,

Shin

• ###### 2. Re: Tool Availability Calculation Help

Hung Nguyen This is a common pitfall and can easily be resolved.

Tableau isn't currently calculating Avail % for those values since the value are empty. They are considered as system NULLS and thus cannot return any result in a calculation.

You can create an intermediate field that imputes zero wherever downtime is unavailable and use it instead of actual downtime in your calculations

Use the following

Hope this helps,

Jeevan Krishna