3 Replies Latest reply on Sep 26, 2016 1:13 PM by Martin Ocando

    Counting a row depending on content of previous or next row field

    Martin Ocando

      I have the following table:

       

      DateEmployee IDNameClock Note
      01/01/20161025Julius Caesar09:00-17:00
      01/02/20161025Julius Caesar09:00-17:30
      01/03/20161025Julius Caesar
      FREE
      01/04/20161025Julius Caesar
      ABSENT
      01/05/20161025Julius Caesar09:02-17:15
      01/06/20161025Julius CaesarABSENT
      01/07/20161025Julius Caesar09:05-17:06

      etc...

       

      I need to count records that were ABSENT before of after his free day. In Oracle there is a Lead and Lag function, but I can't seem to find a way in Tableau. I tried with the following calculated field, but is not working:

       

      (After or Before Free Day)

      IF (TRIM(LOOKUP(ATTR([Clock Note]),-1)) = "FREE" OR

         TRIM(LOOKUP(ATTR([Clock Note]),1)) = "FREE")

      AND

          ATTR([Clock Note]) = "ABSENT"

      THEN

          1

      ELSE

          0

      END

       

      I've attached a workbook that have 3 views:

      - On the 1st view, called Full Table, the Lookup function works ok. It flags every record which have ABSENT and the previous or next day is marked as FREE

      - On the 2nd view, called Absent bf/af Free Day by Date, I filter by Clock Note = "ABSENT", since I only want to see those records where the employee was absent. But the calculation breaks. Is all zeros

      - On the 3rd view, called Absent bf/af Free Day by Employee, I removed the date, since I only want the total days the employee was absent before or after he free day. Same thing, calculation shows zero

       

      BTW, on the main source, the table have over 2K employees, so I need to take into account the employee, so it doesn't take another employee's record as before and after.

       

      Any ideas how I can achieve this?

        • 1. Re: Counting a row depending on content of previous or next row field
          Martin Ocando

          More info:

          At the end, I will also need to calculate to total number of events by employee category that have this condition. Category was not in the test data, but is on the source, so it will be nice to change the level of detail as needed.

           

          BTW, I tried to do { EXCLUDE }, but got the error that I can't use level of detail functions with table calculations.

          • 2. Re: Counting a row depending on content of previous or next row field
            Jonathan Drummey

            See the attached...I simplified your expression a bit (used MIN instead of ATTR) and added an outer table calculation to do the sum.

             

            There are three key points about table calculations to keep in mind:

             

            a) Table calcs are done entirely in Tableau*** and depend on the marks in the view. The marks in the view are determined by the dimensions in the view, so if we remove a dimension (such as taking out the Employee in the ...by Date view) then that is going to return a different set of marks and change the results of the table calculations. So we need to leave the dimensions in the view that are necessary for the table calculation to work.

             

            *** there's a special case involving the TOTAL() table calculation that doesn't apply here.

             

            b) Table calcs are done entirely in Tableau so filters that are applied in the data source (such as regular dimension filters and most filters on regular aggregates) are applied *before* the table calculations are computed. The effect in this case is that a filter on Clock Note removes data from the view that is needed for the After or Before Free Day calc to work. The workaround is to use a table calculation filter.

             

            c) In the case that we need the view to be at a relatively fine grain for the table calculations to work (as is the case here) and we want the final display to be at a coarser grain (as in the two later views) then we can set the viz level of detail to what is necessary for the table calculations to work and then use table calculation filters to get rid of the extra values and end up with what I call the "display level of detail". This is what I set up in both the ...by Date and ...by Employee views.

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: Counting a row depending on content of previous or next row field
              Martin Ocando

              Outstanding. Thanks! Now I need to apply this to my data