4 Replies Latest reply on Oct 9, 2019 3:12 PM by John Sarantos

    Number of missed classes with just a Start Date and Percentage

    cathyo

      I'm using Tableau 2019.2 and workbook is attached along with the data source.

       

      I need to find students that have missed 3 or more classes and all I have is a start date, student's percentage out of 100. The number of missed classes will be checked throughout the term so the number of days a class was held and student's percentage will change depending when it is run. The class is held for 8 weeks and only Monday to Thursday.

       

      I need to figure out how many days a class was held so far before it ends (it will also probably be run after it ends) so I can use the student's percentage to figure out number of missed classes.

       

      1. To get the "End Date" that seems to work, I did "DATE(DATEADD('week',8,DATETRUNC('week',[Start Date], 'Thursday')))."

      2. I need to exclude holidays so they are not counted in the number of days a class is held. I have a holiday calculated field that can be subtracted.

       

      Excel file in the workbook

       

      Nbr of Days (needed calculated field)Term (string)Class (string)
      Possible (string)Start Date (datetime changed to date)End Date (calculated field)ID (string)Student Percentage (string)
      32 minus 2 holidays=30 (class is over)1194TEST 44-1100.01/7/2019  11:06:00 PM2/28/2019192.0
      32 minus 2 holidays=30 (class is over)1196TEST 61-1100.05/20/2019  1:37:36 PM7/11/2019189.0

      1. Run today: 25 days minus 1 holiday=24

      (class is not over so that is why only 25 days Monday to Thursday have been held so far)

       

      2. If run on Monday, August 12th: 26 days minus 1 holiday=25

      1196TEST 63-1100.06/27/2019  2:16:12 PM8/22/2019193.0

       

       

      I've been trying to get this to work for days with no luck so help is very APPRECIATED!  Thank you!