Forum Discussion

andrewhann's avatar
andrewhann
Helper II
8 years ago
Solved

Production Gap

  Can anyone help with the following - I want to measure the time difference between the LASTBOOKDATE and FIRSTBOOKDATE from one Works Order to the next (ie Production Gaps) on each Production...
Need Help
  • fhill's avatar
    fhill
    8 years ago

    The last two references need to be FirstBookDate.
    MAXX = CALCULATE( MAXX(Table1,Table1[LASTBOOKDATE]), FILTER( ALL(Table1), Table1[PRODUCTION_LINE] = EARLIER(Table1[PRODUCTION_LINE]) && Table1[FIRSTBOOKDATE]< EARLIER(Table1[FIRSTBOOKDATE])))
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi andrewhann

is this the expected result?

 

One case

082959  911201A     L1    TimeDifference = 28/12/2016 10:41:00 - 28/12/2016 09:37:00

andrewhann's avatar
andrewhann
Helper II
8 years ago

Sorry - it would have helped if my SQL were correct - there should only be one line per Works Order

 

For consecutive orders (by FIRSTBOOKDATE) on the same production line I want the gap between last order finishing and this order starting

 

 

So in this case - 30/12/16 19:36 - 30/12/16 19:09 ie production gap of 27 minutes on L2

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi andrewhann

     

    This seems very much doable using a measure.

     

    Please post sample data in a table format so i can copy it and write and test the measure.

     

    Else you can share the file

    • andrewhann's avatar
      andrewhann
      Helper II
      8 years ago

      Please find attached sample data

       

      WORDNO_PPSPRODUCT_CODEPRODUCTION_LINEFIRSTBOOKDATELASTBOOKDATE
      706915412985M202/05/2017 13:4702/05/2017 13:49
      706917412985M202/05/2017 14:4702/05/2017 15:05
      708986346490L706/05/2017 00:2706/05/2017 01:31
      708987346490L706/05/2017 08:1606/05/2017 08:18
      709038471838L605/05/2017 11:4209/05/2017 13:11
      709337392870M302/05/2017 15:2002/05/2017 18:18
      709338392870M302/05/2017 19:1702/05/2017 22:36
      709342392870M306/05/2017 13:0206/05/2017 13:45
      709347427140L704/05/2017 04:4204/05/2017 04:46
      709720193239L205/05/2017 11:0305/05/2017 13:14
      709721193239L205/05/2017 10:4305/05/2017 10:56
      709722193239L205/05/2017 12:3205/05/2017 14:32
      709723193239L205/05/2017 14:4605/05/2017 15:32
      709851392870M302/05/2017 11:1702/05/2017 15:06
      709852392870M302/05/2017 22:4902/05/2017 23:42
      709853392870M301/05/2017 22:3601/05/2017 23:19
      709854392870M302/05/2017 07:5302/05/2017 07:55
      709867211100T3003/05/2017 06:2403/05/2017 06:27
      709868211100T3003/05/2017 12:2203/05/2017 13:08
      709869211100T3003/05/2017 17:0003/05/2017 18:14
      709890346340L802/05/2017 01:4402/05/2017 05:24
      709893347320L405/05/2017 09:5805/05/2017 10:43
      709905471322T3004/05/2017 01:2604/05/2017 01:42
      709906471322T3004/05/2017 05:4504/05/2017 07:09
      709907471322T3004/05/2017 11:0604/05/2017 12:47
      709912471800T3002/05/2017 12:1302/05/2017 14:02
      709913471800T3002/05/2017 19:0702/05/2017 19:51
      709919471808L602/05/2017 15:0802/05/2017 16:30
      709927471808L604/05/2017 16:3404/05/2017 19:50
      709940473000L702/05/2017 05:1702/05/2017 10:47
      709941473000L702/05/2017 12:2402/05/2017 18:09
      710020194300L206/05/2017 10:3506/05/2017 10:37
      710024197460L206/05/2017 11:2706/05/2017 11:46
      710026218900L205/05/2017 17:3305/05/2017 18:18
      710027218900L205/05/2017 18:3605/05/2017 19:22
      710029297580M205/05/2017 00:1905/05/2017 01:47
      710030297580M205/05/2017 01:4905/05/2017 02:41
      710057382730L103/05/2017 07:5503/05/2017 08:38
      710058382730L103/05/2017 10:2803/05/2017 10:54
      710059382730L103/05/2017 11:1503/05/2017 11:51
      710060382730L103/05/2017 13:2803/05/2017 14:27
      710063391020L103/05/2017 21:2603/05/2017 21:27
      710064391020L104/05/2017 07:5604/05/2017 08:14
      710065391020L103/05/2017 19:2303/05/2017 19:24
      710066391020L103/05/2017 15:5603/05/2017 15:58
      710067391020L103/05/2017 18:2203/05/2017 18:24
      710068391020L104/05/2017 00:0604/05/2017 00:51
      710069391020L104/05/2017 02:0504/05/2017 02:25
      710070391020L104/05/2017 05:0804/05/2017 05:48
      710071391020L103/05/2017 23:0603/05/2017 23:48
  • fhill's avatar
    fhill
    Resident Rockstar
    8 years ago

    *** Getting Error with your updated sample code.... Working on it ****

     

     

    Here are two Custom Columns (not measures) to help you with your request.  The first Matches Lanes & Finds changes based on FirstBookDateTime (Not Work Order as you menteioned Work Orders can varry in Lanes).

     

    The 2nd takes a DateDiff in Hours (or whatever timeframe you need) if the MAXX value isn't blank.

     

    Thank You,

    FOrrest

     

     

    MAXX = CALCULATE( MAXX(Table1,Table1[LastBookDateTime])
    , FILTER(ALL(Table1), Table1[Lane] = EARLIER(Table1[Lane]) && Table1[FirstBookDateTime] < EARLIER(Table1[FirstBookDateTime])))

     

    Idle Time = IF( ISBLANK( Table1[MAXX] ),0, DATEDIFF(Table1[MAXX],Table1[FirstBookDateTime],HOUR))

     

    • fhill's avatar
      fhill
      Resident Rockstar
      8 years ago

       

      Here's the updated code based on your sample data:

       

       

      MAXX = CALCULATE( MAXX(Table1,Table1[LASTBOOKDATE]), FILTER( ALL(Table1), Table1[PRODUCTION_LINE] = EARLIER(Table1[PRODUCTION_LINE]) && Table1[FIRSTBOOKDATE]< EARLIER(Table1[FIRSTBOOKDATE])))

       

      Idle Time = IF (ISBLANK( Table1[MAXX]),0,

      IF ( Table1[MAXX] = Table1[FIRSTBOOKDATE], 0,
      DATEDIFF(Table1[MAXX],Table1[FIRSTBOOKDATE],HOUR)))

       

       

      • andrewhann's avatar
        andrewhann
        Helper II
        8 years ago

        My code as follows:

         

        MAXX = CALCULATE( MAXX('20and30','20and30'[LASTBOOKDATE]), FILTER( ALL('20and30'), '20and30'[PRODUCTION_LINE] = EARLIER('20and30'[PRODUCTION_LINE]) && '20and30'[FIRSTBOOKDATE]< EARLIER('20and30'[LASTBOOKDATE])))

         

        Idle Time = IF (ISBLANK('20and30'[MAXX]),0,
        IF ('20and30'[MAXX] = '20and30'[FIRSTBOOKDATE], 0,
        DATEDIFF('20and30'[MAXX],'20and30'[FIRSTBOOKDATE],MINUTE)))

         

        Getting an error on Idle Time = "In DATEDIFF function, the start date cannot be greater than the end date"

         

        Due to bad data I know - but unfortunately production line colleagues aren't always the best at recording start and finish times

         

        The issue is missing dates and/or firstdates before previous lastdate due to bad bookings