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

    Tool Availability Calculation Help

    Hung Nguyen

      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!