1 Reply Latest reply on Dec 19, 2016 2:02 PM by diego.medrano

    Date Calculations for uptime

    james.raikes

      Hi,

       

      I am looking at data around the up time of instruments and have a joined database of our install base (Set start point) and when a field that shows status is changed over time and having dificulty in the best way to calculate the time in status. I am looking to measure firstly the days and eventual the number of hour that an instrument is not in "Up" status. Below is an image of the report at the moment. Quick explanation of some of the fields as may help;

       

      Status  - Starting point status

      Dates are from date status is changed

      Replace Null just takes the original status if a serial number has no changes

      Uptime bucket takes different statuses and show if it should be counted as up, down or excluded

      Today Calculation - for the number of days between start point and the change happening

      IFNULL(DATEDIFF('day',[Calc Start Date],[Edit Date]),

      DATEDIFF('day',[Calc Start Date],TODAY()))

       

      Diff to Today - For number of days in period being looked at

      datediff('day',[Calc Start Date],TODAY())

       

      Where I am stuck is how I count this up to show days in each bucket and the % of time in the "Up" bucket. The next problem is if the instrument is in the exclude status for part of the period how do you not count the days in the excluded bucket?

       

       

      Effectively looking for;

      Total number of days for each serial active in reporting period

      Number of days since last change in up time bucket

      % time in up bucket for total days in reporting period

       

      I have attached a sample workbook of the data

       

       

       

      On from this I would like to create a Gantt viz that gives a image for each line that shows the days in the period it was up and down using Bucket in the colour field but think I need to answer the calculation first.

       

      James