1 2 Previous Next 18 Replies Latest reply on Sep 27, 2018 6:07 AM by venkatram

    How to get last timestamp of each shift where shift spans over two days (19:00-06:59)

    Rai Ahmad



      I am trying to calculate the last timestamp of each shift. shifts are run from 0700-1859 & 1900-0659. I have created a calculated column that shows be correct date for the night shift, i.e. a shift that started at 1900 on 02-Jul-2018 will end at 0659 of 03-Jul-2018 but my calculated field changes end date to 02-Jul-2018. This change is needed to calculate the workload of each employee per day, day of when the shift started. I now need a calculated field that gives me last timestamp of each shift. For example, a night shift that started on 2-Jul-2018 19:00 had last activity recorded at 03-Jul-2018 01:41:43 but it took place under 02-Jul-2018 shift so last entry of 2nd July shift would be 01:41. I need Tableau to give me 02-Jul-2018 01:41 as the last timestamp. I have attached data as well.


      It's so much easier to do in Excel, but I cannot figure out how to do it in Tableau.


      So far I have tried following Tableau formulas:




              [3.Shift type],[4.Each Shift exact date] :

                 MAX([5.Each Shift timestamp])



      And following:



              [3.shift type],[4.Each Shift exact date] :

                 MAX([5.Each Shift timestamp])



      Both tableau formulas return multiple records, but I only need one. See the highlighted cell on the attached screenshot. I don't mind having the same record repeated multiple times.


      Following formula in Excel would give me the result I am expecting:


      ={MAX(IF([shift type]="late",IF([each shift exact date]=E2,timestamp of last activity)))}

      I then would use another array formula that returns me a max value if last activity took place before midnight. Attached sheet has formulas in it.


      I would appreciate if someone could help me with this. I would upload a TWBX but the actual dataset is confidential, so I can't share Tableau workbook.

        1 2 Previous Next