6 Replies Latest reply on Oct 7, 2018 7:26 PM by Xu Han

    Calculation using FIX

    Xu Han

      Hi Guys,

      Would deeply appreciate is anyone may help in below calculation. Basically I would like to calculate staff productivity in term of picking lines per min. I need this metric in 4 different levels:

      1. Overall (eg. by day)

      2. Wave (eg. this is like picking batch);

      3. Staff Level

      4. Wave+Staff Level

      Sample data is like below:


      WaveLinePickerPick Time
      001111A28/9/2018 17:46
      001112A28/9/2018 17:49
      001113A28/9/2018 17:49
      001114A28/9/2018 17:50
      001115B28/9/2018 17:51
      001116B28/9/2018 17:53
      001117B28/9/2018 17:54
      001118A28/9/2018 17:55
      002121A28/9/2018 17:45
      002122A28/9/2018 17:50
      002123C28/9/2018 17:53
      002124C28/9/2018 17:56
      002125C28/9/2018 17:58
      003131A28/9/2018 17:43
      003132A28/9/2018 17:49
      003133B28/9/2018 17:51
      003134B28/9/2018 17:54
      003135C28/9/2018 17:55
      003136A28/9/2018 17:57
      003137A28/9/2018 17:58


      I manage to work out { FIXED [Wave]:min([Pick Time])}, { FIXED [Wave]:max([Pick Time])} for my wave time. I then calculate line per min using ([Num_Lines]-COUNTD([Wave]))/SUM([Wave Time]). Reason for subtracting countd(wave) was to deduct first line in each wave

      My overall in 28 Sep is 0.46 line per min seems correct, verified by (8+5+7-3)/(9+13+15)=0.46; And by wave level also seem to be correct, eg. (8-1)/9=0.78



      I took a similar approach for #3 & 4 calculation that involve in staff. I manage to get min and max by staff in each wave, using { FIXED [Wave],[Picker]:min([Pick Time])}.

      However, my end calculation calculation of line/min is wrong. Example Staff A wave 002, Line/Min should be (2-1)/5=0.2


      What I need:

      3. Staff Level

      A/B/C Line/per Min

      4. Wave+Staff Level

      eg. A/001 Line per min, A/002 Line per min.


      Attached a workbook in version 10.5

      Thank you...