10 Replies Latest reply on May 16, 2018 7:43 AM by Shinichiro Murakami

    Creating month bins to generate KPI reports

    Prasad Rao

      I have been trying to replicate KPI reporting functionality in Tableau as we begin to migrate to it from Excel. I have a set of data that contains a list of network assets and any associated alarms with them. Some may have no alarms. Need to calculate availability as follows:

      Availability = Downtime in Month/Minutes in Month

      Downtime is basically (cleared-alarmed). If both are in the same month, it is trivial. However, the alarms do cross months as in row 4 below..

       

      Data looks like below.

       

         

      AssetNameevent_idalarmedcleared
      f-5585-ASA-25535285/2/2017 11:485/2/2017 11:51
      f-5585-ASA-25535535/2/2017 12:335/2/2017 12:36
      f-5585-ASA-25553555/8/2017 11:085/8/2017 13:54
      f-5585-ASA-25863131/31/2017 14:292/1/2017 14:33
      h-103S-fp015506123/22/2017 21:183/22/2017 21:21
      h-103S-fp015558785/8/2017 11:095/8/2017 13:53
      w-5_North_AP-1NULLNULLNULL

      Note that some alarms cross over month as in row 4 above

       

      I would like to specify the Report Start Date and Report End Date and the result I want is below (shown for 01/01/2017 to 05/31/2017):

                 

      AssetNameJan-17Feb-17Mar-17Apr-17May-17
      DowntimeAvailabilityDowntimeAvailabilityDowntimeAvailabilityDowntimeAvailabilityDowntimeAvailability
      f-5585-ASA-2570.598.722%873.997.833%0100.000%0100.000%166.399.627%
      h-103S-fp010100.000%0100.000%3.099.993%0100.000%163.599.634%
      w-5_North_AP-10100.000%0100.000%0100.000%0100.000%0100.000%

       

       

      Been playing with it for a week and cannot seem to get the result I want. Attached the workbook with a small set of data.

       

      How can one accomplish this in Tableau?