2 Replies Latest reply: May 7, 2015 9:32 AM by Bradl Vogl RSS


    Bradl Vogl

      We are trying to refine our job shop scheduling system

      I would like an accumulated total for available hours minus sheduled hours to determine if our work load is leveled.


      60 hours are available per day.

      Each day has many jobs with scheduled times for each.


      I need to subtract each scheduled time from a 60 hour day accumulating the difference to the following day, etc.


      The example below shows "Hrs" for each job

      3/17 would have 60 hours available at days end as there are no "hrs" required.

      4/22 would then start with 120 hours (60 hours carried over) and end with 113.33 (6.67 job subtracted)

      4/30 would then start with 173.33 and end with 172.75



      5/8 has multiple jobs. Each job would be subtracted from the running balance start total efore the next day 60 hours would be added     


      Can anyone help with a solution?


      Thank you,


      Brad Vogl


      Due DatePart #To ShipCustomerOn HandS/OBU/SOBinU/MHrsDescriptionPO #
      03/17/1540802314.0014.00214844212484ASSEMBLY12.00.0003/17/15 B2124849 4080234080232
      04/22/15N17002001-SUB200.00ELES00010.00210774215184ASSEMBLY2.06.6704/22/15 B215184589533
      04/30/151176-000071.00TAPC00010.00214958214957ASSEMBLY35.00.5804/30/15 B214957 1ST ARTPO206932
      05/01/15N451125550213.00OMP000113.00215692215693ASSEMBLY25.00.0005/01/15 B215693 L1703945
      05/05/15M10134-1011.00PREM00011.00216216216217ASSEMBLY4.00.0005/05/15 B216217216216
      05/06/153900002010.00HERA00010.00215717215718ASSEMBLY60.010.0005/06/15 B215718  1ST BL4020155482
      05/08/15GENISYS22.0010.00214991214993ASSEMBLY64.023.4705/08/15 B214993 N169096N16909601
      05/08/15GENISYS30.0010.00214992214996ASSEMBLY64.032.0005/08/15 B214996 N169096N16909601
      05/08/15MLK II22.0010.00214991214995ASSEMBLY44.016.1305/08/15 B214995 N169096N16909601
      05/08/15MLK II30.0010.00214992214998ASSEMBLY44.022.0005/08/15 B214998 N169096N16909601


          Theo Klemming

          Hi Brad


          This problem is not possible to solve entirely using only the functionality available in Datawatch Designer.


          However, if you simplify (aggregate) the data set before loading it into Datawatch Designer in such was that each row contains the total number of hours worked in all jobs for each day, i.e. make the data set contain just 1 row per each work day, then there is a solution. Note that this makes all columns containing details for each job and the specific part obsolete.


          The solution also requires that there is a column containing the number of work hours available for each day, i.e. 60 hours.


          After transforming the data table for time series analysis, you can create a calculated column that for each time point (each day) subtracts the accumulated hours worked from the accumulated daily work hours available per day (60).


          The Calculated column expression will look like this, provided the column names I am using:

          SUM_TIMEWIN("DailyHours", TimeWindowStart, Now)-SUM_TIMEWIN("Scheduled Time Hours", TimeWindowStart, Now)


          "DailyHours" is the column containing the value 60 for each row. "Scheduled Time Hours" is the same column as your "Hrs" column in the sample data.


          Remember, in case your data contains more than 1 row per work day, this will not be correct.

            • Re: ACCUMULATED HOURS
              Bradl Vogl


                           Thank you so much for the response.

                           If I were able to show a running total by day it would work fine.

                           I don't need to drill down to the individual jobs by day.

                           I'm not familiar with time series analysis.

                           I was also contacted by Olly Bond regarding my post. I uploaded some files to him.

                           Thanks again for your detailed response.




              Brad Vogl